package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MysqlConnection {
public static void main(String[] args) {
if(args.length!=4){
System.out.println("输入数据个数要为4");
System.exit(-1);
}
MysqlConnection mc = new MysqlConnection();
//mc.toUpdate();
mc.testPreparStatement(args);
mc.toQuery();
}
public void connectionMysql() {
// 加载驱动
// 创建连接
// 创建sql陈述对象
// 执行sql语句
// 获取结果集
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
// com.mysql.jdbc.Driver
// 加载mysql驱动
Class.forName("com.mysql.jdbc.Driver");
// 创建连接 连接mysql
con = DriverManager
.getConnection("jdbc:mysql://localhost/mysql_test?user=root&password=root123");
// 创建陈述语句对象
stmt = con.createStatement();
// 执行sql语句
rs = stmt.executeQuery("select * from students");
// 遍历结果集中内容
while (rs.next()) {
System.out.println(rs.getString("name"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (con != null) {
con.close();
con = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 连接数据库
* 1.加载驱动
* 2.创建连接
* @return
*/
private static Connection getConnection(){
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/mysql_test?user=root&password=root123");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
/**
* 获取操作数据库的Statement对象
* 问题 Connection Statement 怎样关闭
* @return stmt Statement对象
*/
private static Statement getStatement(){
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?user=root&password=root123");
stmt = conn.createStatement();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}
/**
* 执行查询语句 返回结果集
* 问题 ResultSet 怎样关闭
* @param sql
* @return
*/
public ResultSet getResultSet(String sql){
ResultSet rs = null;
try {
//执行查询语句
rs =getStatement().executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public void toQuery(){
String sql = "select * from students";
MysqlConnection mc = new MysqlConnection();
ResultSet rs = mc.getResultSet(sql);
try {
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs != null){
rs.close();
rs = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 执行修改数据库语句 操作数据库
* @param sql
* @return
*/
public int exeUpdate(String sql){
int in = 0;
try {
in = getStatement().executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return in;
}
public void toUpdate(){
String sql = "insert into students values(null,'吴妹子','女','20','18473819712')";
//String sql ="INSERT INTO `mysql_test`.`students` (`name`, `sex`, `age`, `tel`) VALUES ('刘备', '男', '20', '18473844592')";
MysqlConnection mc = new MysqlConnection();
mc.exeUpdate(sql);
}
public void testPreparStatement(String[] args){
String name = args[0];
String sex = args[1];
int age = Integer.parseInt(args[2]);
String tel = args[3];
String sql = "insert into students values(null,?,?,?,?)";
PreparedStatement ps = null;
try {
//创建预编译语句
ps = getConnection().prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, sex);
ps.setInt(3, age);
ps.setString(4, tel);
//加入批量处理中
ps.addBatch();
//执行批量处理
ps.executeBatch();
//执行DML语句操作
//ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(ps != null){
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}