Java DataBase Connectivity, Java连接数据库,是SUN公司制定的一套接口, java.sql.*;
后期将集成到Mybatis框架中。
从官网下载对应的驱动jar包,将其配置到环境变量classpath。
classpath=.;D:\course\06-JDBC\resources\MySql Connector Java 5.1.23\mysql-connector-java-5.1.23-bin.jar
编程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
| public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ Driver driver = new com.mysql.jdbc.Driver(); DriverManager.registerDriver(driver);
String url = "jdbc:mysql://127.0.0.1:3306/mydatabase"; String user = "root"; String password = "146"; conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
int count = stmt.executeUpdate("update dept set dname = '销售部', loc = '合肥' where deptno = 20;"); } catch(SQLException e) { e.printStackTrace(); } finally { if(stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
|
处理返回结果
executeUpdate:insert/update/delete, 返回结果对表文件操作时,受影响行数
executeQuery:查询命令【select * from 表名】,返回结果是查询命令得到【临时表】,ResultSet实例对象。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| ResultSet rs = null; rs = ps.executeQuery();
while(rs.next()){ String empno = rs.getString(1); String ename = rs.getString(2); String sal = rs.getString(3); System.out.println(empno + "," + ename + "," + sal); String empno = rs.getString("empno"); String ename = rs.getString("ename"); String sal = rs.getString("sal"); System.out.println(empno + "," + ename + "," + sal); int empno = rs.getInt(1); String ename = rs.getString(2); double sal = rs.getDouble(3); System.out.println(empno + "," + ename + "," + (sal + 100)); int empno = rs.getInt("empno"); String ename = rs.getString("ename"); double sal = rs.getDouble("sal"); System.out.println(empno + "," + ename + "," + (sal + 200)); }
|
使用动态资源绑定器传参
1 2 3 4 5 6 7 8
| ResourceBundle bundle = ResourceBundle.getBundle("jdbc"); String driver = bundle.getString("driver"); String url = bundle.getString("url"); String user = bundle.getString("user"); String password = bundle.getString("password");
Connection conn = null; conn = DriverManager.getConnection(url,user,password);
|
1 2 3 4
| driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mydatabase user=root password=146
|
sql注入
问题:用户输入语句含有sql语句关键字,完成了sql语句的拼接,参与编译,导致原sql语句含义被扭曲。
解决方法:用户提供的信息不参与编译过程,用java.sql.PreparedStatement(继承java.sql.Statement)
- PreparedStatement属于预编译的数据库操作对象,原理:预先对sql语句的框架进行编译,再给sql语句传“值”,使用较多。
- PreparedStatement效率高。只用预编译一次,可执行多次。在编译阶段做安全检查(ps.setString())Statement执行一次编译一次
- 要求支持sql语句拼接时使用Statement。(如升/降序是在语句末尾拼接asc/desc)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
| Connection conn = null;
PreparedStatement ps = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/mydatabase", "root", "146"); String sql = "select * from t_user where userName = ? and userPassword = ?"; ps = conn.prepareStatement(sql); ps.setString(1,userLoginInfo.get("userName")); ps.setString(2,userLoginInfo.get("userPassword")); rs = ps.executeQuery(); if(rs.next()) { loginSuccess = true; } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (ps != null) { try { ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } }
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
| import java.sql.*; import java.util.Scanner;
Scanner s = new Scanner(System.in); System.out.println("请输入desc或者asc"); String keyWords = s.nextLine();
Connection conn = null; Statement stmt = null; ResultSet rs = null;
try { Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/mydatabase", "root", "146"); stmt = conn.createStatement(); String sql = "select ename from emp order by ename " + keyWords; rs = stmt.executeQuery(sql);
while(rs.next()){ System.out.println(rs.getString("ename")); }
} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stmt != null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
|
JDBC事务自动提交
即执行任一条DML语句,则自动提交一次。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
|
public static void main(String[] args) { Connection conn = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/mydatabase", "root", "146");
conn.setAutoCommit(false);
String sql = "update t_act set balance = ? where actno = ? "; ps = conn.prepareStatement(sql); ps.setInt(1,10000); ps.setDouble(2,111);
int count = ps.executeUpdate();
ps.setInt(1,10000); ps.setDouble(2,222); count += ps.executeUpdate();
System.out.println(count == 2 ? "转账成功" : "转账失败");
conn.commit(); } catch (Exception e) { if (conn != null) { try { conn.rollback(); } catch (SQLException throwables) { throwables.printStackTrace(); } } e.printStackTrace(); } finally { if (ps != null) { try { ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
|
锁
行级锁(悲观锁):for update 当前事务结束之前,行结果被锁住,无法修改。事务排队进行,不允许并发
乐观锁:多线程并发,事务不需要排队,都可以修改,需要版本号。
DAO与Entity
DAO(Data Access Object) 数据访问对象,将数据库操作都封装起来。包括:
1)实体类Dept:用于存放与传输对象数据。
2)数据库连接和关闭工具类JdbcUtil: 避免了数据库连接和关闭代码的重复使用,方便修改。
3)DAO 实现类DeptDao: 针对不同数据库给出DAO接口定义方法的具体实现。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168
| package com.bjpowernode.entity; public class Dept { private Integer deptNo; private String dname; private String loc;
public Integer getDeptNo() {return deptNo;} public void setDeptNo(Integer deptNo) {this.deptNo = deptNo;} public String getDname() {return dname;} public void setDname(String dname) {this.dname = dname;} public String getLoc() {return loc;} public void setLoc(String loc) {this.loc = loc;} public Dept() { } public Dept(Integer deptNo, String dname, String loc) { this.deptNo = deptNo; this.dname = dname; this.loc = loc; } }
public class JdbcUtil { private Connection con = null; private PreparedStatement ps=null; static{ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public Connection createCon(){ try { con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/bjpowernode", "root", "123"); } catch (SQLException e) { e.printStackTrace(); System.out.println("Connection对象创建失败"); } return con; } public PreparedStatement createStatement(String sql){ Connection con = createCon(); try { ps = con.prepareStatement(sql); } catch (SQLException e) { e.printStackTrace(); } return ps; } public void close(){ if(ps!=null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(con!=null){ try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } public void close(ResultSet rs){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } close();
} }
public class DeptDao { private JdbcUtil util = new JdbcUtil(); public int add(String deptNo,String dname,String loc){ String sql="insert into dept (deptNo,dname,loc) values(?,?,?)"; int result=0; PreparedStatement ps = util.createStatement(sql); try { ps.setInt(1, Integer.valueOf(deptNo)); ps.setString(2, dname); ps.setString(3, loc); result=ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { util.close(); } return result; }
public int delete(String deptNo){ String sql ="delete from dept where deptno=?"; PreparedStatement ps = util.createStatement(sql); int result = 0; try { ps.setInt(1, Integer.valueOf(deptNo)); result = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { util.close(); } return result; }
public int update(String deptNo,String dname,String loc){ String sql ="update dept set dname=?,loc=? where deptno=?"; PreparedStatement ps = util.createStatement(sql); int result=0; try { ps.setString(1, dname); ps.setString(2, loc); ps.setInt(3, Integer.valueOf(deptNo)); result = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { util.close(); } return result; }
public List findAll(){ String sql ="select * from dept"; PreparedStatement ps = util.createStatement(sql); ResultSet rs = null; List list = new ArrayList(); try { rs = ps.executeQuery(); while(rs.next()){ int deptNo = rs.getInt("deptno"); String dname = rs.getString("dname"); String loc = rs.getString("loc"); Dept dept = new Dept(deptNo, dname, loc); list.add(dept); } } catch (SQLException e) { e.printStackTrace(); } finally { util.close(rs); } return list; } }
|