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();

}

}

}


}