JDBC连接MySQL(干货)

JAVA连接JDBC操作数据库

什么是JDBC

Java DataBase Connectivity，Java数据库连接，jdbc实际是java中的一套和数据库交互的API(application program interface 应用程序编程接口)

为什么使用JDBC

因为Java程序员需要连接多种数据库，为了避免每一个数据库都学习一套新的API，sun公司提出了一个JDBC接口，各个数据库厂商根据此接口写实现类（驱动），这样java程序员只需要掌握JDBC接口中的一套方法，就可以访问任何数据库。 导包： import java.sql.*

如何使用JDBC

外网访问:https://mvnrepository.com/ 1. 从maven网站上搜索mysql 找到5.1.6版本的坐标，把坐标复制到工程的pom.xml文件中 ，在pom.xml文件源码里面project标签内部 添加<dependecies>标签，把复制的内容粘贴到里面 ，保存在工程中出现奶瓶即可、 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> 2. 创建Demo01.java 在main方法中写以下代码： //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2. 获取连接对象 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3","root","root"); //System.out.println(conn); //3.创建执行SQL语句的对象 Statement stat = conn.createStatement(); //4.执行SQL String sql = "create table if not exists jdbct1(id int primary key auto_increment,name varchar(10))"; stat.execute(sql); System.out.println("执行完成"); //5.关闭资源,先打开的资源后关闭 stat.close(); conn.close();

执行SQL的方法

execute(sql): 此方法可以执行任意SQL，但是推荐执行DDL(create drop alter truncate) ,方法的返回值为布尔值,返回值代表是否有结果集，'true':有结果集，'false':没有结果集 executeUpdate(sql): (DML)此方法执行增删改的SQL，方法返回值为int类型，代表生效(ok)行数 executeQuery(sql): 此方法执行select查询的SQL，返回值为ResultSet结果集

解析配置文件

配置文件位置： src/main/resources 配置文件名：jdbc.properties driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/newdb3 username=root password=123456 initialSize=2 maxActive=10

解析文件：

/** * 解析Properties后缀文件，获取对应的信息 * properties后缀文件是以键值对方式存储信息 */ //创建属性对象 Properties prop= new Properties(); //得到文件输入流'Demo05表示类名' InputStream ips=Demo05.class.getClassLoader().getResourceAsStream("jdbc.properties"); //把流交给属性对象 prop.load(ips); //获取数据,只能获取到字符串类型的数据 String name = prop.getProperty("name"); String age = prop.getProperty("age"); System.out.println(name+": "+age); //注册驱动以及设置连接池 ResultSet 获取数据的方式有两种： 1、通过查询到的字段名称获取数据 rs.getString("ename"); 2、通过查询到的字段位置获取数据 rs.getString(2); 数据库类型和java类型对比： MySQL java int getInt() varchar getString() float/double getFloat()/getDouble() datetime/timestamp getDate()

数据库连接池DBCP

DataBase Connection Pool:数据库连接池 为什么使用：如果没有数据库连接池，每次业务都需要和数据库建立一次连接，用完之后断开，如果有1万次业务请求则有1万次连接的开和关，频繁开关连接，浪费资源，使用数据库连接池可以将连接重用，避免资源的浪费 如何使用数据库连接池 1. 下载jar包： 从maven服务器中搜索 dbcp <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> 2. 把坐标复制粘贴到pom.xml中 3. 代码如下： //创建数据源对象 BasicDataSource dataSource = new BasicDataSource(); //设置数据库连接信息 dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/newdb3"); dataSource.setUsername("root"); dataSource.setPassword("123456"); //设置初始连接数量 dataSource.setInitialSize(3); //设置最大连接数量 dataSource.setMaxActive(5); //从连接池中得到连接 Connection conn = dataSource.getConnection(); System.out.println(conn);

@Test 测试单元注解 只能对无参无返回值的方法进行单元测试

SQL的注入风险

select count(*) from t_user where username='libai' and password='admin1'; SQL注入的演示 执行语句 String sql = "insert into jdbcuser values(null,'"+name+"',"+age+")"; 输入：' or '1'='1 输出SQL拼接语句： select count(*) from t_user where username='asdfsdf' and password='' or '1'='1' 出现登陆成功状态

PreparedStatement对象

预编译的SQL对象 1. 为什么PreparedStatement可以避免SQL注入？ 因为预编译的SQL执行对象 在创建stat对象的时候已经将SQL进行编译并且把SQL语句的逻辑固定，不会因为后期替换进去的内容改变SQL语句 String sql = "select count(*) from t_user where username=? and password=?"; stat = conn.prepareStatement(sql); //替换问号的值 stat.setString(1, username); stat.setString(2, password); //执行SQL rs = stat.executeQuery(); '不需要传入参数' 2、好处 1）. 代码更直观避免了拼接SQL语句 2）. 可以避免SQL注入 3）. 执行效率略高于statement 如果SQL语句中存在变量则使用PreparedStatement，如果没有变量则使用Statement

批量操作

将多次和数据库服务器的数据交互合并成一次，提高执行效率 '添加到批量操作中' stat.addBatch(sql1); stat.addBatch(sql2); stat.addBatch(sql3); '执行批量操作' stat.executeBatch();

事务

1、设置自动提交的状态 conn.setAutoCommit(false/true); 2、提交 conn.commit(); 3、回滚 conn.rollback(); 案例：实现超人有5000 蝙蝠侠100 让超人给蝙蝠侠转账2000，程序第一次运行转账成功 第二次转账成功第三次转账失败 create table person(id int primary key auto_increment,name varchar(10), money int); insert into person values(null,'超人',5000),(null,'蝙蝠侠',100); 获取自增主键的值 Statement stat.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS); PreparedStatement stat = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 获取主键值 rs =stat.getGeneratedKeys(); while(rs.next()){ //得到返回的主键值 int key=rs.getInt(1); System.out.println("自动生成的ID："+key); }

获取元数据

元数据包括：数据库的元数据和表的元数据 1. 数据库元数据里面保存一些和数据库相关的信息 如:数据库厂商名称,数据库驱动版本,数据库连接地址,用户名等等 //得到数据库的元数据 DatabaseMetaData dbmd = conn.getMetaData(); System.out.println("驱动版本:"+dbmd.getDriverVersion()); System.out.println("连接地址:"+dbmd.getURL()); System.out.println("用户名:"+dbmd.getUserName()); System.out.println("数据库厂商:"+dbmd.getDatabaseProductName()); 2. 表的元数据里面保存一些和表相关的信息 如：表有多少字段，每个字段的名称、类型等 //从结果集中得到表的元数据 ResultSetMetaData rsmd = rs.getMetaData(); //得到表字段的数量 int count = rsmd.getColumnCount(); //得到每个字段的字段名和类型 for (int i = 0; i < count; i++) { String name = rsmd.getColumnName(i+1); String type = rsmd.getColumnTypeName(i+1); System.out.println(name+":"+type); }

JDBC练习源代码

JAV中连接JDBC需要导包： import java.sql.***; '必须导入java.sql.下面的包，否则会出错' 1、注册驱动 Class.forName("com.mysql.jdbc.Driver"); 2、获取连接对象 Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3","root", "123456"); 3、创建执行SQL语句对象 Statement stat=conn.createStatement(); 4、执行SQL String sql = "create table if not exists jdbct1(id int primary key auto_increment,name varchar(10))"; stat.execute(sql); //查询表中的数据，见到ResultSet就是用while循环 String sql="select * from emp"; rs=stat.executeQuery(sql); while(rs.next()){ int id=rs.getInt("empno"); String name=rs.getString("ename"); int sal=rs.getInt("sal"); System.out.println("ID:"+id+" 姓名："+name+" 工资："+sal); } 5、关闭资源 stat.close(); conn.close(); 6、解析jdbc.properties配置文件'文件路径src/main/resources' 解析Properties后缀文件，获取对应的信息,properties后缀文件是以键值对方式存储信息 例如： driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/newdb3 username=root password=123456 //创建属性对象 Properties prop= new Properties(); //得到文件输入流,Demo05当前类名 InputStream ips=Demo05.class.getClassLoader().getResourceAsStream("jdbc.properties"); //把流交给属性对象 prop.load(ips); //获取数据,只能获取到字符串类型的数据 String username = prop.getProperty("username"); String password = prop.getProperty("password"); System.out.println(username+": "+password); 7、创建数据库连接池 //创建数据源对象 BasicDataSource dataSource= new BasicDataSource(); //设置数据库连接信息 dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/newdb3"); dataSource.setUsername("root"); dataSource.setPassword("123456"); //设置初始连接数量 dataSource.setInitialSize(3); //设置最大连接数量 dataSource.setMaxActive(5); //从连接池中得到连接 Connection conn= dataSource.getConnection(); System.out.println(conn); 8、解析配置文件与连接池配合使用 private static BasicDataSource dataSource; static{ //创建属性对象 Properties prop= new Properties(); //得到文件输入流 InputStream ips=Demo05.class.getClassLoader().getResourceAsStream("jdbc.properties"); //把流交给属性对象 try { prop.load(ips); //获取数据,只能获取到字符串类型的数据 String driver = prop.getProperty("driver"); String url = prop.getProperty("url"); String username = prop.getProperty("username"); String password = prop.getProperty("password"); /**创建数据库连接池*/ //创建数据源对象 dataSource= new BasicDataSource(); //设置数据库连接信息 dataSource.setDriverClassName(driver); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); //设置初始连接数量 dataSource.setInitialSize(3); //设置最大连接数量 dataSource.setMaxActive(5); } catch (IOException e) { e.printStackTrace(); } } //从连接池中得到连接 public static Connection getConn() throws SQLException{ return dataSource.getConnection(); } //关闭打开的资源 public static void close(Connection conn,Statement stat,ResultSet rs){ try { if(rs!=null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(stat!=null){ stat.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(conn!=null){ //保证自动提交处于打开状态,必要时会在程序中关闭自动提交 conn.setAutoCommit(true); conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } 9、连接池做测试 public class demo02 { public static void main(String[] args) throws SQLException { threadTest t1=new threadTest(); t1.start(); threadTest t2=new threadTest(); t2.start(); threadTest t3=new threadTest(); t3.start(); threadTest t4=new threadTest(); t4.start(); threadTest t5=new threadTest(); t5.start(); } } class threadTest extends Thread{ @Override public void run(){ //获取连接 try { Connection conn = DBUtils.getConn(); System.out.println("获取连接！"); //模拟耗时 Thread.sleep(5000); //归还连接 conn.close(); System.out.println("归还连接！"); } catch (SQLException e) { e.printStackTrace(); } catch(Exception e){ e.printStackTrace(); } } } 10、PreparedStatement使用测试 public static void main(String[] args) { Scanner scan=new Scanner(System.in); System.out.println("请输入用户名："); String name = scan.nextLine(); System.out.println("请输入年龄："); int age = Integer.parseInt(scan.nextLine()); Connection conn = null; //预编译的SQL执行对象 PreparedStatement stat = null; ResultSet rs = null; try { conn = DBUtils.getConn(); String sql = "insert into jdbcuser values(null,?,?)"; stat = conn.prepareStatement(sql); //把？ 替换成真正的数据 stat.setString(1, name); stat.setInt(2, age); //执行SQL语句 stat.executeUpdate(); System.out.println("插入数据成功！"); } catch (Exception e) { e.printStackTrace(); } finally { DBUtils.closeSource(conn, stat, rs); } } 11、批量插入练习 public static void main(String[] args) { Connection conn = null; Statement stat = null; ResultSet rs = null; try { conn = DBUtils.getConn(); stat = conn.createStatement(); String sql1="insert into jdbcuser values(null,'张三',20)"; String sql2="insert into jdbcuser values(null,'李四',21)"; String sql3="insert into jdbcuser values(null,'王五',22)"; //stat.executeUpdate(sql1); //stat.executeUpdate(sql2); //stat.executeUpdate(sql3); //添加到批量操作中 stat.addBatch(sql1); stat.addBatch(sql2); stat.addBatch(sql3); //执行批量操作 stat.executeBatch(); System.out.println("插入数据成功！"); } catch (Exception e) { e.printStackTrace(); } finally { DBUtils.closeSource(conn, stat, rs); } } public static void main(String[] args) { Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { long time0 = System.currentTimeMillis(); conn = DBUtils.getConn(); String sql = "insert into jdbcuser values(null,?,?)"; stat = conn.prepareStatement(sql); for(int i=1;i<=100;i++){ stat.setString(1,"name"+i); //替换 ? stat.setInt(2, 100+i); //添加到批量操作 stat.addBatch(); //每20条执行一次 if(i%20==0){ //执行批量操作 stat.executeBatch(); //清除批量操作 stat.clearBatch(); } } stat.executeBatch(); long time1 = System.currentTimeMillis(); System.out.println("插入数据成功！耗时："+(time1 - time0)+"ms"); } catch (Exception e) { e.printStackTrace(); } finally { DBUtils.closeSource(conn, stat, rs); } } 12、分页查询 public static void main(String[] args) { Scanner scan=new Scanner(System.in); System.out.println("请输入查询页数："); int page= Integer.parseInt(scan.nextLine()); if(page<=0){ //查询页数不可以为负数 page=1; } System.out.println("请输入查询条数："); int num = Integer.parseInt(scan.nextLine()); Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { conn = DBUtils.getConn(); String sql = "select * from jdbcuser limit ?,?"; stat = conn.prepareStatement(sql); stat.setInt(1,(page-1)*num); stat.setInt(2, num); rs=stat.executeQuery(); while(rs.next()){ int id=rs.getInt("id"); String name = rs.getString("name"); System.out.println("ID:"+id+" name:"+name); } System.out.println("插入数据成功！"); } catch (Exception e) { e.printStackTrace(); } finally { DBUtils.closeSource(conn, stat, rs); } } 13、转账练习 public static void main(String[] args) { Connection conn = null; Statement stat = null; ResultSet rs = null; try { conn = DBUtils.getConn(); conn.setAutoCommit(false); stat =conn.createStatement(); String sql1 = "update person set money=money-2000 where id=1"; String sql2 = "update person set money=money+2000 where id=2"; stat.executeUpdate(sql1); stat.executeUpdate(sql2); //得到超人剩余的钱数，如果大于等于0，则允许转账，否则回滚 rs=stat.executeQuery("select money from person where id=1"); while(rs.next()){ int money = rs.getInt("money"); if(money>=0){ System.out.println("转账成功！"); conn.commit(); //提交 } else{ System.out.println("转账失败！"); conn.rollback(); //回滚 } } System.out.println("操作成功！"); } catch (Exception e) { e.printStackTrace(); } finally { DBUtils.closeSource(conn, stat, rs); } } 13、向表中插入数据,队名并且获取主键，根据主键插入队员，实现关系表 public static void main(String[] args) { //createTable(); Scanner scan=new Scanner(System.in); System.out.println("请输入球队名称："); String teamName = scan.nextLine(); System.out.println("请输入球员名称："); String playName = scan.nextLine(); scan.close(); Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { conn = DBUtils.getConn(); /** * 查询是否存在用户输入的球队 */ String sql = "select * from team where name=?"; stat = conn.prepareStatement(sql); stat.setString(1, teamName); rs = stat.executeQuery(); int tid =-1; while(rs.next()){ //得到已经保存的球队id tid = rs.getInt("id"); } //判断是否保存过球队 if(tid==-1){ sql = "insert into team values(null,?)"; stat.close(); //获取主键值 stat = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); stat.setString(1, teamName); stat.executeUpdate(); rs.close(); //得到主键值 rs = stat.getGeneratedKeys(); while(rs.next()){ tid = rs.getInt(1); System.out.println("teamId="+tid); } } //插入球员 sql = "insert into player values(null,?,?)"; stat.close(); stat = conn.prepareStatement(sql); stat.setString(1, playName); stat.setInt(2, tid); //执行 stat.executeUpdate(); System.out.println("操作成功！"); } catch (Exception e) { e.printStackTrace(); } finally { DBUtils.closeSource(conn, stat, rs); } } 14、获取元数据 public static void main(String[] args) { Connection conn = null; Statement stat = null; ResultSet rs = null; try { conn = DBUtils.getConn(); //得到数据库的元数据 DatabaseMetaData dbmd=conn.getMetaData(); System.out.println("驱动版本："+dbmd.getDriverMajorVersion()); System.out.println("链接地址："+dbmd.getURL()); System.out.println("用户名："+dbmd.getUserName()); System.out.println("数据库厂商："+dbmd.getDatabaseProductName()); stat=conn.createStatement(); rs = stat.executeQuery("select * from emp"); //从结果集体中得到表的元数据 ResultSetMetaData rsmd=rs.getMetaData(); int count = rsmd.getColumnCount(); //得到表字段的数量 System.out.println("表字段数量："+count); //得到每个字段的字段名和类型 for(int i=0;i<count;i++){ String name = rsmd.getColumnName(i+1).toLowerCase(); String type= rsmd.getColumnTypeName(i+1).toLowerCase(); System.out.println(name+" "+type); } System.out.println("操作成功！"); } catch (Exception e) { e.printStackTrace(); } finally { DBUtils.closeSource(conn, stat, rs); } }