数据库-JDBC学习笔记
JDBC概念
全称：java database connectivity
中文名：java数据库连接
本质：java语言操作数据库
JDBC定义了操作所有操作关系型数据库（mysql，oracle，...)的规则（接口），各个数据库厂商提供数据库驱动jar包来实现这套接口（实现类）。
也就是说，我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。
第一个简单的JDBC程序：
基本步骤：
1.注册驱动 DriverManager
2.获取数据库连接对象 Connection
3.定义sql语句
4.获取执行sql的对象 Statement
5.执行sql语句 executeUpdate()
6.查看执行结果
7.释放资源close()
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.SQLException; 4 import java.sql.Statement; 5 6 public class Jdbcdemo01 { 7 public static void main(String[] args) throws ClassNotFoundException, SQLException { 8 9 //注册驱动 10 Class.forName("com.mysql.jdbc.Driver"); 11 //获取数据库连接对象 12 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fdxsql","root","0322"); 13 //定义sql语句 14 String sql = "update boy set age = 28 where id =1 "; 15 //获取执行sql的对象 Statement 16 Statement stmt = conn.createStatement(); 17 //执行sql 18 int count = stmt.executeUpdate(sql); 19 //处理结果 20 System.out.println(count); 21 //关闭数据库 22 stmt.close(); 23 conn.close(); 24 25 } 26 27 }
DriverManager
是一个驱动管理类
1.注册驱动：
告诉程序该使用哪一个数据驱动jar
内含静态方法 registerDriver（Driver driver）。但是具体写代码时，我们常常只写Class.forName("com.mysql.jdbc.Driver");
因为com.mysql.jdbc.Driver类（mysql对Driver接口的实现类）中包含一个静态代码块，里面就包含了registerDriver（Driver driver）方法注册驱动的过程。
2.获取数据库连接
DriverManager类的对象通过调用getConnection(String url,String user,String password)方法来获取数据库连接，返回的是Connection类型。
三个参数：url为指定连接的路径，语法为：jdbc:mysql://IP地址（域名）:端口号/想连接的数据库名称;
如果连接的是本机的mysql服务器，可以简写为jdbc:mysql:///想连接的数据库名称；
user与password即为注册mysql时的用户名与密码。
Connection
是一个数据库连接类
1.获取执行sql的对象
通过getConnection（）方法得到Connection对象后，
Connection对象可以调用createStatement()方法，返回一个Statement对象
也可以调用prepareStatement（String sql）方法返回一个PreparedStatement对象
2.管理事务
开启事务：setAutoCommit（boolean aotoCommit）设置为false则开启事务
提交事务：commit（）
回滚事务：rollback（）放在catch中
1 Connection conn = null; 2 PreparedStatement pstmt1 = null; 3 PreparedStatement pstmt2 = null; 4 try { 5 conn = JDBCutils.getConnection(); 6 conn.setAutoCommit(false); 7 String sql1 = "update account set balance = balance - ? where id = ?"; 8 String sql2 = "update account set balance = balance + ? where id = ?"; 9 pstmt1 = conn.prepareStatement(sql1); 10 pstmt2 = conn.prepareStatement(sql2); 11 pstmt1.setDouble(1,500); 12 pstmt1.setInt(2,1); 13 pstmt2.setDouble(1,500); 14 pstmt2.setInt(2,2); 15 16 pstmt1.executeUpdate(); 17 pstmt2.executeUpdate(); 18 conn.commit(); 19 } catch (Exception e) { 20 try { 21 if(conn!=null) { 22 conn.rollback(); 23 } 24 } catch (SQLException e1) { 25 e1.printStackTrace(); 26 } 27 e.printStackTrace(); 28 }finally { 29 JDBCutils.close(pstmt1, conn); 30 JDBCutils.close(pstmt2, null); 31 }
Statement
执行sql语句的类
1.boolean execute（String sql）可以执行任何sql语句，不常用，了解即可
2.int executeUpdate(String sql) 执行DML（update，delete，insert）语句（常用）
返回值是执行语句影响的行数，可以用来判断DML语句是否执行成功，返回值>0则执行成功，反之失败
规范代码示例：
1 Connection conn = null; 2 Statement stmt = null; 3 try { 4 Class.forName("com.mysql.jdbc.Driver"); 5 conn = DriverManager.getConnection("jdbc:mysql:///fdxsql","root","0322"); 6 String sql = "insert into boy values(null,'小华',38)"; 7 stmt = conn.createStatement(); 8 int count = stmt.executeUpdate(sql); 9 if (count>0) { 10 System.out.println("添加成功！"); 11 }else { 12 System.out.println("添加失败！"); 13 } 14 }catch (ClassNotFoundException e) { 15 e.printStackTrace(); 16 }catch (SQLException e) { 17 e.printStackTrace(); 18 }finally { 19 if(stmt!=null){ 20 try { 21 stmt.close(); 22 }catch (SQLException e) { 23 e.printStackTrace(); 24 } 25 } 26 if(conn!=null){ 27 try { 28 conn.close(); 29 }catch (SQLException e) { 30 e.printStackTrace(); 31 } 32 } 33 }
DDL（create，alter，drop）语句（不常用，一般由数据库直接执行），无返回结果
规范代码示例：
1 Connection conn = null; 2 Statement stmt = null; 3 try { 4 Class.forName("com.mysql.jdbc.Driver"); 5 conn = DriverManager.getConnection("jdbc:mysql:///fdxsql","root","0322"); 6 String sql = "create table jdbctest(id int,name varchar(20))"; 7 stmt = conn.createStatement(); 8 int count = stmt.executeUpdate(sql); 9 System.out.println(count);//DDL语句无返回值，一般返回0 10 }catch (ClassNotFoundException e) { 11 e.printStackTrace(); 12 }catch (SQLException e) { 13 e.printStackTrace(); 14 }finally { 15 if(stmt!=null){ 16 try { 17 stmt.close(); 18 }catch (SQLException e) { 19 e.printStackTrace(); 20 } 21 } 22 if(conn!=null){ 23 try { 24 conn.close(); 25 }catch (SQLException e) { 26 e.printStackTrace(); 27 } 28 } 29 }
3.ResultSet executeQuery(String sql) 执行DQL（select）语句
返回的是ResultSet类型的结果，成为结果集对象，封装了查询的结果。
从ResultSet结果集中获取查询内容的说明：
首先我们要知道ResultSet类中的boolean next( )方法，默认游标位于字段行，调用该方法可以使游标向下移动一行，从而分行获取数据。
因为next( )方法返回的是布尔类变量，若当前行是最后一行末尾（无数据可查询了），则会返回false，否则返回true，可用来判断游标位置。
具体获取数据时，调用getInt（索引值），getString（索引值/索引标签）等方法将数据存放到相应的数据类型变量中即可，注意索引从1开始。
具体代码如下：
1 Connection conn = null; 2 Statement stmt = null; 3 ResultSet rs = null; 4 try { 5 Class.forName("com.mysql.jdbc.Driver"); 6 conn = DriverManager.getConnection("jdbc:mysql:///fdxsql","root","0322"); 7 String sql = "SELECT * FROM girl"; 8 stmt = conn.createStatement(); 9 rs = stmt.executeQuery(sql); 10 11 while(rs.next()) { 12 int age = rs.getInt(1); 13 String name = rs.getString(2); 14 System.out.println(name+"--->"+age); 15 } 16 17 }catch (ClassNotFoundException e) { 18 e.printStackTrace(); 19 }catch (SQLException e) { 20 e.printStackTrace(); 21 }finally { 22 if(rs!=null){ 23 try { 24 rs.close(); 25 }catch (SQLException e) { 26 e.printStackTrace(); 27 } 28 } 29 if(stmt!=null){ 30 try { 31 stmt.close(); 32 }catch (SQLException e) { 33 e.printStackTrace(); 34 } 35 } 36 if(conn!=null){ 37 try { 38 conn.close(); 39 }catch (SQLException e) { 40 e.printStackTrace(); 41 } 42 } 43 }
练习：封装一个方法，查询emp表的数据，将其封装为一个个对象，然后装载在容器中，返回该容器。
1 public class TestEmp { 2 public static void main(String[] args) { 3 List <Emp>list = new TestEmp().findAll(); 4 System.out.println(list); 5 } 6 public List<Emp> findAll(){ 7 Connection conn = null; 8 Statement stmt = null; 9 ResultSet rs = null; 10 List <Emp>list =null; 11 try { 12 Class.forName("com.mysql.jdbc.Driver"); 13 conn = DriverManager.getConnection("jdbc:mysql:///exercise1","root","0322"); 14 String sql = "SELECT * FROM emp"; 15 stmt = conn.createStatement(); 16 rs = stmt.executeQuery(sql); 17 18 Emp emp = null; 19 list = new ArrayList <Emp>(); 20 21 while(rs.next()) { 22 int id = rs.getInt(1); 23 String name = rs.getString(2); 24 int job_id = rs.getInt(3); 25 int mgr = rs.getInt(4); 26 Date joindate = rs.getDate(5); 27 double salary = rs.getDouble(6); 28 double bonus = rs.getDouble(7); 29 int dept_id = rs.getInt(8); 30 emp = new Emp(); 31 emp.setId(id); 32 emp.setName(name); 33 emp.setJob_id(job_id); 34 emp.setMgr(mgr); 35 emp.setJoindate(joindate); 36 emp.setSalary(salary); 37 emp.setBonus(bonus); 38 emp.setDept_id(dept_id); 39 list.add(emp); 40 41 } 42 43 }catch (ClassNotFoundException e) { 44 e.printStackTrace(); 45 }catch (SQLException e) { 46 e.printStackTrace(); 47 }finally { 48 if(rs!=null){ 49 try { 50 rs.close(); 51 }catch (SQLException e) { 52 e.printStackTrace(); 53 } 54 } 55 if(stmt!=null){ 56 try { 57 stmt.close(); 58 }catch (SQLException e) { 59 e.printStackTrace(); 60 } 61 } 62 if(conn!=null){ 63 try { 64 conn.close(); 65 }catch (SQLException e) { 66 e.printStackTrace(); 67 } 68 } 69 } 70 return list; 71 72 } 73 74 }
练习：写一个工具类，简化JDBC主程序操作：
工具类中包含：
1.通过properties文件预设好的url，user，password，driver信息，封装一个静态代码块，再定义一个方法来注册驱动，获取数据库连接对象
2.通过重载的方式封装不同的释放资源操作。
properties文件：
1 url= jdbc:mysql:///exercise1 2 user= root 3 password= 0322 4 driver = com.mysql.jdbc.Driver
utils工具类：
1 public class JDBCutils { 2 private static String url; 3 private static String user; 4 private static String password; 5 private static String driver; 6 static { 7 Properties pro = new Properties(); 8 try { 9 pro.load(new FileReader("src/jdbc.properties")); 10 url = pro.getProperty("url"); 11 user = pro.getProperty("user"); 12 password = pro.getProperty("password"); 13 driver = pro.getProperty("driver"); 14 Class.forName(driver); 15 16 } catch (FileNotFoundException e) { 17 e.printStackTrace(); 18 } catch (IOException e) { 19 e.printStackTrace(); 20 } catch (ClassNotFoundException e) { 21 e.printStackTrace(); 22 } 23 } 24 25 public static Connection getConnection() throws SQLException{ 26 return DriverManager.getConnection(url, user, password); 27 28 } 29 30 public static void close(Statement stmt,Connection conn) { 31 if(stmt!=null){ 32 try { 33 stmt.close(); 34 }catch (SQLException e) { 35 e.printStackTrace(); 36 } 37 } 38 if(conn!=null){ 39 try { 40 conn.close(); 41 }catch (SQLException e) { 42 e.printStackTrace(); 43 } 44 } 45 } 46 47 public static void close(Statement stmt,Connection conn,ResultSet rs) { 48 if(rs!=null){ 49 try { 50 rs.close(); 51 }catch (SQLException e) { 52 e.printStackTrace(); 53 } 54 } 55 if(stmt!=null){ 56 try { 57 stmt.close(); 58 }catch (SQLException e) { 59 e.printStackTrace(); 60 } 61 } 62 if(conn!=null){ 63 try { 64 conn.close(); 65 }catch (SQLException e) { 66 e.printStackTrace(); 67 } 68 } 69 } 70 71 }
工具类的应用：
1 public class useUtils { 2 public static void main(String[] args) { 3 List <Emp>list = new useUtils().findAllutils(); 4 System.out.println(list); 5 } 6 public List<Emp> findAllutils(){ 7 Connection conn = null; 8 Statement stmt = null; 9 ResultSet rs = null; 10 List <Emp>list =null; 11 try { 12 conn=JDBCutils.getConnection(); 13 String sql = "SELECT * FROM emp"; 14 stmt = conn.createStatement(); 15 rs = stmt.executeQuery(sql); 16 17 Emp emp = null; 18 list = new ArrayList <Emp>(); 19 20 while(rs.next()) { 21 int id = rs.getInt(1); 22 String name = rs.getString(2); 23 int job_id = rs.getInt(3); 24 int mgr = rs.getInt(4); 25 Date joindate = rs.getDate(5); 26 double salary = rs.getDouble(6); 27 double bonus = rs.getDouble(7); 28 int dept_id = rs.getInt(8); 29 emp = new Emp(); 30 emp.setId(id); 31 emp.setName(name); 32 emp.setJob_id(job_id); 33 emp.setMgr(mgr); 34 emp.setJoindate(joindate); 35 emp.setSalary(salary); 36 emp.setBonus(bonus); 37 emp.setDept_id(dept_id); 38 list.add(emp); 39 } 40 41 }catch (SQLException e) { 42 e.printStackTrace(); 43 }finally { 44 JDBCutils.close(stmt, conn, rs); 45 } 46 return list; 47 } 48 }
练习：通过键盘录入用户名和密码，判断用户是否登录成功（存在SQL注入问题）
1 public class LogIn { 2 public static void main(String[] args) { 3 Scanner sc = new Scanner(System.in); 4 System.out.println("请输入用户名"); 5 String username = sc.nextLine(); 6 System.out.println("请输入密码"); 7 String password = sc.nextLine(); 8 boolean flag = new LogIn().login(username, password); 9 if (flag) { 10 System.out.println("登录成功"); 11 }else { 12 System.out.println("登录失败"); 13 } 14 15 } 16 17 18 public boolean login(String username,String password) { 19 if(username==null||password==null) { 20 return false; 21 } 22 Connection conn = null; 23 Statement stmt = null; 24 ResultSet rs = null; 25 try { 26 conn = JDBCutils.getConnection(); 27 String sql = "SELECT * FROM user where username = '"+username+"' and password = '"+password+"'"; 28 stmt = conn.createStatement(); 29 rs = stmt.executeQuery(sql); 30 return(rs.next()); 31 } catch (SQLException e) { 32 e.printStackTrace(); 33 }finally { 34 JDBCutils.close(stmt, conn, rs); 35 } 36 return false; 37 } 38 }
PreparedStatement
注入问题：输入用户随意，输入密码a' or 'a' ='a
则sql语句为SELECT * FROM user where username = '"+username+"' and password = 'a' or 'a' ='a'
通过PreparedStatement替换Statement对象解决。
预编译的SQL：参数使用？作为占位符
步骤：1.注册驱动 DriverManager
2.获取数据库连接对象 Connection
3.定义sql语句：参数用？代替
4.获取执行sql的对象 PreparedStatement
5.用setxxx（位置编号，？的值）方法给？赋值
5.执行sql语句 executeUpdate()，无参
6.处理结果
7.释放资源close()
1 public class newLogin { 2 public static void main(String[] args) { 3 Scanner sc = new Scanner(System.in); 4 System.out.println("请输入用户名"); 5 String username = sc.nextLine(); 6 System.out.println("请输入密码"); 7 String password = sc.nextLine(); 8 boolean flag = new newLogin().newlogin(username, password); 9 if (flag) { 10 System.out.println("登录成功"); 11 }else { 12 System.out.println("登录失败"); 13 } 14 15 } 16 17 public boolean newlogin(String username,String password) { 18 if(username==null||password==null) { 19 return false; 20 } 21 Connection conn = null; 22 PreparedStatement pstmt = null; 23 ResultSet rs = null; 24 try { 25 conn = JDBCutils.getConnection(); 26 String sql = "SELECT * FROM user where username = ? and password = ?"; 27 pstmt = conn.prepareStatement(sql); 28 pstmt.setString(1,username); 29 pstmt.setString(2,password); 30 31 rs = pstmt.executeQuery(); 32 return(rs.next()); 33 } catch (SQLException e) { 34 e.printStackTrace(); 35 }finally { 36 JDBCutils.close(pstmt, conn, rs); 37 } 38 return false; 39 } 40 }
数据库连接池
概念：一个存放数据库连接Connection对象的容器。
当系统初始化完成后，容器被创建，容器中会申请一些连接对象。当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。
优点：节约资源，用户访问高效。
实现：标准接口：DataSource
方法：获取连接getConnection（），归还连接Connection.close()
一般由数据库厂商来实现
1.C3P0：数据库连接池技术
2.Druid：数据库连接池技术，由阿里巴巴提供
C3P0
数据库连接池技术
步骤：1.导入jar包（2个）
2.定义配置文件：
名称：c3p0.properties 或者 c3p0-config.xml
路径：直接将文件放在src目录下即可。
3.创建核心对象 数据库连接池对象 ComboPooledDataSource
4.获取连接：getConnection
一般我们使用默认配置，也可以自定义配置，然后再创建连接池对象时，指定自定义配置即可。
1 DataSource ds = new ComboPooledDataSource();
2 Connection conn = ds.getConnection();
3 System.out.println(conn);
配置内容：
1 <c3p0-config> 2 <default-config> 3 <property name="driverClass">com.mysql.jdbc.Driver</property> 4 <property name="jdbcUrl">jdbc:mysql://localhost：3306/fdxsql</property> 5 <property name="user">root</property> 6 <property name="password">root</property> 7 8 <property name="initialPoolSize">5</property> 9 <property name="maxPoolSize">10</property> 10 <property name="checkoutTimeout">3000</property> 11 </default-config> 12 13 14 <named-config name="otherc3p0"> 15 <property name="driverClass">com.mysql.jdbc.Driver</property> 16 <property name="jdbcUrl">jdbc:mysql://localhost：3306/fdxsql</property> 17 <property name="user">root</property> 18 <property name="password">root</property> 19 20 <property name="initialPoolSize">5</property> 21 <property name="maxPoolSize">8</property> 22 <property name="checkoutTimeout">3000</property> 23 </named-config> 24 25 </c3p0-config>
Druid
数据库连接池实现技术，由阿里巴巴提供
步骤：1.导入jar包：druid-1.0.9.jar
2.定义配置文件
3.加载配置文件 Properties
4.获取数据库连接池对象：通过工厂来获取 DruidDataSourceFactory
5.获取连接：getConnection
1 //加载配置文件 2 Properties pro = new Properties(); 3 InputStream is = Testdruid.class.getClassLoader().getResourceAsStream("druid.properties"); 4 pro.load(is); 5 //获取数据库连接池对象 6 DataSource ds=DruidDataSourceFactory.createDataSource(pro); 7 //获取连接 8 Connection conn = ds.getConnection(); 9 System.out.println(conn);
Druid工具类：
1.定义一个类druidUtils
2.提供静态代码块加载配置文件，初始化连接池对象
3.提供方法：获取连接方法，释放资源，获取连接池的方法
1 public class druidUtils { 2 private static DataSource ds; 3 static { 4 try { 5 //加载配置文件 6 Properties pro = new Properties(); 7 pro.load(JDBCutils.class.getClassLoader().getResourceAsStream("druid.properties")); 8 ds=DruidDataSourceFactory.createDataSource(pro); 9 }catch (IOException e) { 10 e.printStackTrace(); 11 }catch (Exception e) { 12 e.printStackTrace(); 13 } 14 } 15 16 public static Connection getConnection() throws SQLException { 17 return ds.getConnection(); 18 } 19 public static void close(Statement stmt,Connection conn) { 20 close(null,stmt,conn); 21 } 22 public static void close(ResultSet rs,Statement stmt,Connection conn) { 23 if(rs!=null) { 24 try { 25 rs.close(); 26 } catch (SQLException e) { 27 e.printStackTrace(); 28 } 29 } 30 if(stmt!=null) { 31 try { 32 stmt.close(); 33 } catch (SQLException e) { 34 e.printStackTrace(); 35 } 36 } 37 if(conn!=null) { 38 try { 39 conn.close(); 40 } catch (SQLException e) { 41 e.printStackTrace(); 42 } 43 } 44 } 45 public static DataSource getDataSource() { 46 return ds; 47 } 48 }
工具类测试：
1 //给account表添加一条记录 2 Connection conn = null; 3 PreparedStatement pstmt = null; 4 try { 5 conn = druidUtils.getConnection(); 6 String sql = "insert into account values(null,?,?)"; 7 pstmt=conn.prepareStatement(sql); 8 //给？赋值 9 pstmt.setString(1,"王五"); 10 pstmt.setDouble(2,3000); 11 int count = pstmt.executeUpdate(); 12 System.out.println(count); 13 } catch (SQLException e) { 14 e.printStackTrace(); 15 }finally { 16 druidUtils.close(pstmt, conn); 17 }
Spring JDBC
Spring框架对JDBC的简单封装，提供了一个JDBCTemplate对象简化JDBC的开发
步骤：
1.导入jar包
2.创建JdbcTemplate对象，依赖于数据源DataSource
JdbcTemplate Template = new JdbcTemplate（ds）;
3.调用JdbcTemplate的方法来完成CRUD的操作
update():执行DML语句，增删改语句
queryForMap():查询结果，将结果集封装为map集合，结果集长度只能为1，列名作为key，值作为value
queryForList():查询结果，将结果集封装为list集合，将每一条数据封装为一个Map集合，再将Map集合装载到list
query():查询结果，将结果集封装为Javabean对象
query的参数：（sql，RowMapper）
一般我们使用BeanPropertyRowMapper实现类，可以完成数据到JavaBean的自动封装
new BeanPropertyRowMapper<类型>（类型.class）
queryForObject():查询结果，将结果集封装为对象，一般用于聚合函数查询
练习：
1.修改1号数据的salary为10000
1 JdbcTemplate template = new JdbcTemplate(druidUtils.getDataSource()); 2 String sql = "update emp set salary = 10000 where id = 1001"; 3 int count = template.update(sql); 4 System.out.println(count);
2.添加一条记录
1 JdbcTemplate template = new JdbcTemplate(druidUtils.getDataSource()); 2 String sql = "insert into emp(id,ename,dept_id) values(?,?,?)"; 3 int count = template.update(sql,1015,"郭靖",10); 4 System.out.println(count);
3.删除刚才添加的记录
1 JdbcTemplate template = new JdbcTemplate(druidUtils.getDataSource()); 2 String sql = "delete from emp where id = ?"; 3 int count = template.update(sql,1015); 4 System.out.println(count);
4.查询id为1的记录，将其封装为Map集合
一个Map对象只能存放一条数据，列名作为key，值作为value
1 JdbcTemplate template = new JdbcTemplate(druidUtils.getDataSource()); 2 String sql = "select * from emp where id = ?"; 3 Map<String,Object>map = template.queryForMap(sql,1001); 4 System.out.println(map);
5.查询所有记录，将其封装为List
每一条数据都是一个map，再存放到一个list中
1 JdbcTemplate template = new JdbcTemplate(druidUtils.getDataSource()); 2 String sql = "select * from emp"; 3 List<Map<String,Object>> list = template.queryForList(sql); 4 for(Map<String,Object> stringObjectMap:list) { 5 System.out.println(stringObjectMap);
6.查询所有记录，将其封装为Emp对象的List集合
1 JdbcTemplate template = new JdbcTemplate(druidUtils.getDataSource()); 2 String sql = "select * from emp"; 3 List<Emp> list = template.query(sql,new BeanPropertyRowMapper<Emp>(Emp.class)); 4 for(Emp emp:list) { 5 System.out.println(emp); 6 }
7.查询总记录数
1 JdbcTemplate template = new JdbcTemplate(druidUtils.getDataSource()); 2 String sql = "select count(id) from emp"; 3 Long total = template.queryForObject(sql,long.class); //一般用来执行聚合函数 4 System.out.println(total);
