2 java ee(2)----JDBC

1 使用JDBC连接MySQL数据库

（1）Class.forName(com.jdbc.mysql.Driver);   括号里的类是引进的jar包里的包名.类名

（2）Connection ct = DriverManager.getConnection(url,userName,usePassword);

           url为标准写法，每一个数据库都不同； userName和userPassword为数据库用户名和数据库密码

    Connection为借口，这里是接口的引用指向实现它的类的对象

（3）执行sql语句

2 用Statement或preparedStatement 

（1）preparedStatemnet执行sql语句，语句在被DB的与编译器编译后的执行代码缓存下来，下次调用时直接调用预编译后的执行代码，而不用再编译；SQL语句可以带参数；

（2）CallableStatement实现了statement和preparedStatement，用来调用数据库中的存储过程

 

public class Test10JDBC {
public void conn(){
Connection c = null;
try {
Class.forName("com.mysql.jdbc.Driver");
   c = DriverManager.getConnection("jdbc:mysql://localhost:3306/first_db?useSSL=true","root","root");
System.out.println("加载驱动成功");
} catch (Exception e) {
// TODO Auto-generated catch block
System.out.println("加载驱动失败");
e.printStackTrace();
}
try {
/*
* Statement 增删改查
java.sql.Statement stmt = c.createStatement();

// 批处理,一次执行2条语句

stmt.addBatch(sql1);

stmt.addBatch(sql2);

stmt.executeBatch();

stmt.executeUpdate("insert into user(userId,userName) values('1','1')");
stmt.executeUpdate("delete from user where userId = '1'");
stmt.executeUpdate("update user set userName = 'hahaha' where userId = '12345'");
ResultSet rs = stmt.executeQuery("select* from user ");
while(rs.next()){
System.out.println("从数据库中取出："+rs.getString("userName"));
}
*/

// PreparedStatement增删改查
String sql1 = "insert into user(userId,userName)value(?,?)" ;
String sql2 = "update user set userName = ? where userId = ? ";
String sql3 = "delete from user where userId = ?";
String sql4 = "select from user where userId = ?";
java.sql.PreparedStatement pstmt = c.prepareStatement(sql3);

pstmt.setString(1, "1122");
pstmt.setString(2, "wowowo");

//批处理 pstmt.addBatch();  pstmt.executeBatch();

pstmt.setString(1, "1122");

pstmt.setString(1, "h");
pstmt.setString(2,"12345" );

pstmt.setString(1,"12345" );

pstmt.executeUpdate();
System.out.println("去数据库看看");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("再改改");
e.printStackTrace();
}
}

（1）PreparedStatementyo

public class Test10JDBC {
public void conn(){
Connection c = null;
try {
Class.forName("com.mysql.jdbc.Driver");
   c = DriverManager.getConnection("jdbc:mysql://localhost:3306/first_db?useSSL=true","root","root");
System.out.println("加载驱动成功");
} catch (Exception e) {
// TODO Auto-generated catch block
System.out.println("加载驱动失败");
e.printStackTrace();
}
try {
/*
* Statement 增删改查
java.sql.Statement stmt = c.createStatement();
stmt.executeUpdate("insert into user(userId,userName) values('1','1')");
stmt.executeUpdate("delete from user where userId = '1'");
stmt.executeUpdate("update user set userName = 'hahaha' where userId = '12345'");
ResultSet rs = stmt.executeQuery("select* from user ");
while(rs.next()){
System.out.println("从数据库中取出："+rs.getString("userName"));
}
*/

// PreparedStatement增删改查
String sql1 = "insert into user(userId,userName)value(?,?)" ;
String sql2 = "update user set userName = ? where userId = ? ";
String sql3 = "delete from user where userId = ?";
String sql4 = "select from user where userId = ?";
java.sql.PreparedStatement pstmt = c.prepareStatement(sql3);

pstmt.setString(1, "1122");
pstmt.setString(2, "wowowo");

pstmt.setString(1, "1122");

pstmt.setString(1, "h");
pstmt.setString(2,"12345" );

pstmt.setString(1,"12345" );

pstmt.executeUpdate();
System.out.println("去数据库看看");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("再改改");
e.printStackTrace();
}
}

4 JDBC处理transaction（事务）：例子：银行转账A账户执行update语句    B账户执行update语句，必须同时执行或同时不执行，不能存在中间态   

sql语句执行完后会自动提交，autoCommit,默认为true。解决办法是：

（1）在所有sql语句之前：conn.setAutoCommit(false);

（2）在所有sql语句之后：conn.commit();      conn.setAutoCommit(true);

（3）恢复现场：在catch （Exception e）{

conn.rooback();

conn.setAutoCommit(true);

}

*5 可滚动的结果集：结果不是一条一条往上，也是可以前进可以后退可以跳跃的查询结果

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,Result.CONCUR_READ_ONLY)    

前一个参数：对回滚不敏感的    后一个参数：并发时只读

rs.getString(10);或rs.getString("wangxin")；   拿出第10条

*6 可更新的结果集：改变结果，然后它自动去DB中更新