package jdbc;
import java.sql.*;
/*
JDBC基础流程
导入mysql jar包
Class.forname()
DriverManager.getConnection(url,user,pwd)
Statement stmt=con.createStatement()
ResultSet rs= stmt.excuteQuery(sql)
JDBC中的类：
Connection
Statement createStatement();
Statement createStatement(RSType,RSConcurrency);
PrepareStatement prepareStatement(sql)
setAutoCommit(boolean)是否自动提交 若为false 则需调用 conn.commit()方法
rollback() 回滚操作一般写在catch
Statement
ResultSet executeQuery(String sql) SELECT
int executeUpdate(String sql) INSERT UPDATE DELETE
boolean execute(String sql) 动态SQL 可用于执行创建数据库，创建表的SQL语句
Close() 若关闭Connection对象 Statement 会自动close()
PrepareStatement
相比Statement 可以动态提供/接收参数
所有参数都由? 作为占位符 执行前必须为每个参数赋值
setXXX(parameterindex,value);
ResultSet executeQuery(String sql)
int executeUpdate(String sql)，int excuteUpdate()
int[] executeBatch() , clearBatch();
boolean execute(String sql)
Close()
CallableStatement
执行对数据库存储过程的调用
ResultSet
boolean next() 将光标移动到下一行 boolean previous()移动到上一行
boolean first() 移动到第一行 last()移动到最后一行
beforeFirst() 移动到第一行之前 afterLast() 移动到最后一行之后
boolean absolute(int row)移动到指定的行 boolean relative(int row)相对移动指定行数
int getRow() 返回光标指定的行
使用光标需要指定createStatement中RSType 和RSConcurrency参数
getXXX(int colindex/String colname) colindex从1开始
getArray(int colindex/String colname)
updateRow()更新数据库中当前行
deleteRow()
refreshRow() 刷新数据集
cancelRowUpdates() 取消对当前行所做的任何更新
insertRow() 在数据库中插入一行 只有当光标指向插入行时才能调用此方法
*/
public class StudentsManager {
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";
static final String USER = "root";
static final String PWD = "123456";
public static void main(String[] args) {
//Student 是自定义的类
Student stu1 = new Student();
stu1.setStuId("202102102");
stu1.setName("胡哈哈");
stu1.setSex("男");
stu1.setStuclass("JSJKX01");
stu1.setAcademy("计算机学院");
stu1.setTel("11100001111");
stu1.setEmail("hhhhhh@hh.com");
stu1.setAddress("长沙");
Student stu2 = new Student();
stu2.setStuId("202101202");
stu2.setName("刘 星");
stu2.setSex("男");
stu2.setTel("10023009090");
stu2.setEmail("liuxing@null.com");
stu2.setStuclass("KSJKX02");
stu2.setAcademy("计算机学院");
stu2.setAddress("北京市");
Student stu3=new Student("202101101","张 三","女","22222222222","zhangsan@null.com","RJGC01","计算机学院","保定");
Student[] stus = {stu1, stu2,stu3};
// addData(stus);
// updateData(stus);
getData();
}
public static void getData() {
Connection conn = null;
Statement stmt = null; //静态SQL语句
ResultSet rs = null;
try {
//STEP1：注册驱动程序，将驱动程序的类文件加载到内存当并将其注册
//方法I
Class.forName(JDBC_DRIVER);
// 方法II 如果使用的的是JDK非兼容的JVM(Microsoft)则应使用registerDriver()
/*
Driver myDriver = new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver( myDriver );
*/
//STEP2： 获取连接对象
conn = DriverManager.getConnection(DB_URL, USER, PWD);
//STEP3: 创建SQL语句
String sql = "Select * from students";
//STEP4: 创建Statement
//RSType 光标可以向前向后 RSConurrency 创建只读结果集
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//STEP5: 创建ResultSet
rs = stmt.executeQuery(sql);
//STEP6：获取ResultSet数据
while (rs.next()) {
String stuid = rs.getString(1);
String name = rs.getString(2);
String sex = rs.getString(3);
String tel = rs.getString("tel");
String email = rs.getString("email");
String stuclass = rs.getString("class");
String academy = rs.getString("academy");
String address = rs.getString("address");
System.out.println(stuid + "\t" + name + "\t" + sex + "\t" + tel + "\t" + email + "\t" + stuclass + "\t" + academy + "\t" + address + "\t");
}
rs.first();
System.out.println("rs.frist() name:" + rs.getString("name"));
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void addData(Student stu) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PWD);
String sql = " INSERT INTO students (stu_id,`name`,sex,tel,email,class,academy,address) " +
"VALUES (?,?,?,?,?,?,?,?) ";
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
pstmt.setString(1, stu.getStuId());
pstmt.setString(2, stu.getName());
pstmt.setString(3, stu.getSex());
pstmt.setString(4, stu.getTel());
pstmt.setString(5, stu.getEmail());
pstmt.setString(6, stu.getStuclass());
pstmt.setString(7, stu.getAcademy());
pstmt.setString(8, stu.getAddress());
if (0 < pstmt.executeUpdate()) {
System.out.println("Successfully Added!");
}
conn.commit();
pstmt.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
public static void addData(Student[] stus) {
Connection conn = null;
PreparedStatement stmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PWD);
String sql = " INSERT INTO students (stu_id,`name`,sex,tel,email,class,academy,address) " +
"VALUES (?,?,?,?,?,?,?,?) ";
stmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
for (Student stu : stus) {
stmt.setString(1, stu.getStuId());
stmt.setString(2, stu.getName());
stmt.setString(3, stu.getSex());
stmt.setString(4, stu.getTel());
stmt.setString(5, stu.getEmail());
stmt.setString(6, stu.getStuclass());
stmt.setString(7, stu.getAcademy());
stmt.setString(8, stu.getAddress());
stmt.addBatch();
}
int[] updCounts = new int[stus.length];
if (updCounts == stmt.executeBatch()) {
System.out.println("Successfully added all data !");
}
conn.commit();
stmt.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
/*
* 根据stuid更改学生信息
* */
public static void updateData(Student stu) {
Connection conn = null;
PreparedStatement stmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PWD);
String sql = " UPDATE students SET name = ?,sex = ? ,tel = ?,email = ? ,class=?,academy=?,address=? " +
" where stu_id=?";
stmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
stmt.setString(1, stu.getName());
stmt.setString(2, stu.getSex());
stmt.setString(3, stu.getTel());
stmt.setString(4, stu.getEmail());
stmt.setString(5, stu.getStuclass());
stmt.setString(6, stu.getAcademy());
stmt.setString(7, stu.getAddress());
stmt.setString(8, stu.getStuId());
if (0 < stmt.executeUpdate()) {
System.out.println("Successfully Updated!");
}
conn.commit();
stmt.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void updateData(Student[] stus) {
Connection conn = null;
PreparedStatement stmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PWD);
String sql = " UPDATE students SET name = ?,sex = ? ,tel = ?,email = ? ,class=?,academy=?,address=? " +
" where stu_id=?";
stmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
for (Student stu : stus) {
stmt.setString(1, stu.getName());
stmt.setString(2, stu.getSex());
stmt.setString(3, stu.getTel());
stmt.setString(4, stu.getEmail());
stmt.setString(5, stu.getStuclass());
stmt.setString(6, stu.getAcademy());
stmt.setString(7, stu.getAddress());
stmt.setString(8, stu.getStuId());
stmt.addBatch();
}
int[] resultCounts = stmt.executeBatch();
conn.commit();
stmt.clearBatch();
for(int i =0;i<resultCounts.length;i++){
System.out.println(resultCounts[i]);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
try {
if (conn != null)
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void deleteData(String stuid) {
Connection conn = null;
PreparedStatement stmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PWD);
String sql = "DELETE FROM students WHERE stu_id = ?";
stmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
stmt.setString(1, stuid);
if (0 < stmt.executeUpdate()) {
System.out.println("Successfully Deleted!");
}
conn.commit();
stmt.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void deleteData(String[] stusid){
Connection conn = null;
PreparedStatement stmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PWD);
String sql = "DELETE FROM students WHERE stu_id = ?";
stmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
for (String stuid:stusid) {
stmt.setString(1, stuid);
}
int [] resultCount=stmt.executeBatch();
if (resultCount.length == stmt.executeUpdate()) {
System.out.println("Successfully Deleted All Data!");
}
conn.commit();
stmt.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}