JDBC API 详解
DriverManager(驱动管理类)作用:
1.注册驱动
2.获取数据库连接
1.注册驱动
Class.forName(“com.mysql。jdbc.Driver”)
2.获取连接
static Connectioon getConnection(String url,String user,String password)
(1)url:连接路径
(2)user:用户名
(3)password:密码
//注册驱动Class.forName("com.mysql.jdbc.Driver");//获取连接String url = "jdbc:mysql://localhost:3306/db?useSSL=false&serverTimezone=CST";String username = " ";String password = " ";Connection conn = DriverManager.getConnection(url, username,password);
Connection(数据库连接对象)作用:
1.获取执行SQL的对象
2.管理事务
1.获取执行SQL对象
(1)普通执行SQL对象
Statement createStatement()
(2)预编辑SQL的执行SQL对象:防止SQL注入
PreparedStatement prepareStatement(sql)
(3)执行存储过程的对象
CallableStatement prepareCall(sql)
//定义sqlString sql1 = "update account set money = 4000 where id =1";String sql2 = "update account set money = 4000 where id =2";//获取执行sql的对象StatementStatement stmt = conn.createStatement();
2.事务管理
开启事务:setAutoCommit(boolean autoCommit):true为自动提交事务;false为手动提交事务,即为开启事务
提交事务:commit()
回滚事务:rollback()
try {//开启事务conn.setAutoCommit(false);//执行sqlint count1 = stmt.executeUpdate(sql1);//受影响的行数//处理结果System.out.println(count1);//执行sqlint count2 = stmt.executeUpdate(sql2);//受影响的行数//处理结果System.out.println(count2);//提交事务conn.commit();} catch (Exception throwables) {// TODO Auto-generated catch block//回滚事务conn.rollback();throwables.printStackTrace();}
Statement作用:
执行SQL语句
执行SQL语句
int executeUpdate(sql):执行DML、DDL语句
返回值:(1)DML语句影响的行数(2)DDL语句执行后,执行成功也可能返回0
ResultSet executeQuery(sql):执行DQL语句
返回值:ResultSet结果集对象
ResultSet(结果集对象)作用:
==封装了SQL查询语句的结果==
@Test public void testResultSet2() throws Exception { //1. 注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2. 获取连接: String url = "jdbc:mysql:///db?useSSL=false"; String username = ""; String password = ""; Connection conn = DriverManager.getConnection(url, username, password); //3. 定义sql String sql = "select * from account"; //4. 获取statement对象 Statement stmt = conn.createStatement(); //5. 执行sql ResultSet rs = stmt.executeQuery(sql); // 创建集合 List list = new ArrayList(); // 6.1 光标向下移动一行,并且判断当前行是否有数据 while (rs.next()){ Account account = new Account(); //6.2 获取数据 getXxx() int id = rs.getInt("id"); String name = rs.getString("name"); double money = rs.getDouble("money"); //赋值 account.setId(id); account.setName(name); account.setMoney(money); // 存入集合 list.add(account); } System.out.println(list); //7. 释放资源 rs.close(); stmt.close(); conn.close(); }
PreparedStatement 作用: 预编译 SQL 语句并执行:预防 SQL 注入问题
SQL注入: SQL 注入是通过操作输入来修改事先定义好的 SQL 语句,用以达 到执行代码对服务器进行攻击的方法。
// 接收用户输入 用户名和密码 String name = "qweqweq"; String pwd = "' or '1' = '1"; String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'"; System.out.println(sql); // 获取stmt对象 Statement stmt = conn.createStatement(); // 执行sql ResultSet rs = stmt.executeQuery(sql); // 判断登录是否成功 if(rs.next()){ System.out.println("登录成功~"); }else{ System.out.println("登录失败~"); }
使用 PreparedStatement 改进
@Testpublic void testPreparedStatement() throwsException { //2. 获取连接: String url = "jdbc:mysql:///db?useSSL=false"; String username = ""; String password = ""; Connection conn = DriverManager.getConnection(url, username,password); // 接收用户输入 用户名和密码 String name = "zhangsan"; String pwd = "' or '1' = '1"; // 定义sql String sql = "select * from tb_user where username = ? and password = ?"; // 获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql); // 设置?的值 pstmt.setString(1,name); pstmt.setString(2,pwd); // 执行sql ResultSet rs = pstmt.executeQuery(); // 判断登录是否成功 if(rs.next()){ System.out.println("登录成功~"); }else{ System.out.println("登录失败~"); } //7. 释放资源 rs.close(); pstmt.close(); conn.close();}
PreparedStatement 好处: 预编译 SQL ,性能更高 防止 SQL 注入: == 将敏感字符进行转义 ==
转义的SQL如下:
select * from tb_user where username = 'zhangsan' and password = '\'or \'1\' = \'1'
在获取PreparedStatement对象时,将sql语句发送给mysql服务器进行检查,编译(这些步骤很耗时) 执行时就不用再进行这些步骤了,速度更快。