JDBC概念:使用Java语言操作关系型数据库的一套api(可以用一套标准的jdbc操作所有类型的数据库,jdbc是接口,每一个数据库(mysql、oracle、db2...)都去实现jdbc的接口,每个数据库的实现类都不一样,这些实现类也称为驱动,例如,下载mysql驱动即jar包,就是下载mysql的实现类)
mysql驱动jar包https://downloads.mysql.com/archives/c-j/下载第二个,完解压后 将”mysql-connector-java-5.1.48.jar“导入到idea
步骤:
1.注册驱动(idea不识别jar)(mysql 5之后的驱动包可以不写,在jar包META-INF\services\java.sql.Driver里有记录)
Class.forName("com.mysql.jdbc.Driver");-- Driver源码有静态代码块,加载registerDriver注册驱动
2.获取连接
Connnection conn = DriverManager.getConnection(url,username,password);
3.定义SQL语句
String sql = "updata ... ";
4.获取执行SQL对象
Statement stmt = conn.createStatement();
5.执行SQL(就是把sql语句发送给mysql,让mysql执行语句)
stmt.executeUpdate(sql);
6.处理返回结果
7.释放资源
package t1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class jdbc { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false"; String username = "root"; String password = "1234"; Connection conn = DriverManager.getConnection(url,username,password); String sql1 = "update account set money = 3000 where id = 1"; String sql2 = "update account set money = 3000 where id = 2"; Statement statement = conn.createStatement(); try { conn.setAutoCommit(false); int i1 = statement.executeUpdate(sql1); System.out.println(i1); int i2 = statement.executeUpdate(sql2); System.out.println(i2); conn.commit(); } catch (Exception throwables) { conn.rollback(); throwables.printStackTrace(); } statement.close(); conn.close(); } }
语法:jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2&参数键值对3...
示例:jdbc:mysql://127.0.0.1:3306/db1?useSSL=false
红色部分为协议即固定格式,后面为ip地址、端口号、连接哪个数据库,useSSL=false不提示红色警告
如果为默认地址,可以简写jdbc:mysql:///db1
JDBC API详解:
DriverManager:驱动管理类,作用:1.注册驱动,2.获取数据库连接
Connection:数据库连接对象,作用:1.获取执行SQL对象,2.管理事务
管理事务:注意此处指的是jdbc的事务管理,需要使用trycatch进行事务管理
开启事务:setAutoCommit(boolean autoCommit):true为自动提交事务,false为手动提交事务(一般为false,如果用了true,后面就不用再写commit()了,会报错)
提交事务:commit()
回滚事务:rollback()
Statement:执行SQL语句,executeUpdate(sql)执行DML、DDL语句,executeQuery(sql)执行DQL语句(这个返回的是resultSet类型的值,下面会提到)
在执行DML时,会返回影响行数,若为0则没有修改到,另外需要注意最终结果需要反馈给用户,即需要做一个判断,若返回影响行数大于0,则打印 修改成功,反之 修改失败。
在执行DDL时,例如删除表时,返回的是0,在操作DDL时,只需要不报错即可
ResultSet:executeQuery(sql)执行DQL语句(比较常用)
while(rs.next()){-- next方法是索引,自动往下判断是否有数据
rs.getXxx(参数)-- 参数可以是int也可以是字段名,int从左往右从1开始,Xxx为返回的数据类型
}
PreparedStatement:预编译sql语句并执行,用于防止sql注入(injection)的(防止别人输入特定的语句登录账号密码,进行破坏)
以后直接写preparedStatement,注意要传sql,并set参数,不用再写createStatement,下面有示例
作用:1.性能更快:预编译功能,2.防止sql注入:将敏感字符进行转义
原理:
预编译功能需要手动开启:useServerPrepStmts=true;(默认是关闭的)
查看:
将下面这段代码粘贴进mysql的安装路径里的my.ini(注意不能出现回车和中文双引号),并重启mysql服务
log-output=FILE
general-log=1
general_log_file="D:\mysql.log"
slow-query-log=1
slow_query_log_file="D:\mysql_slow.log"
long_query_time=2
粘贴完后,cmd打开命令符services.msc,重启mysql服务
关注D:\mysql.log文件内的变化
idea中输入
package t1; import java.sql.*; public class j1 { public static void main(String[] args) throws SQLException { String url = "jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true"; String username = "root"; String password = "1234"; Connection con = DriverManager.getConnection(url, username, password); //Statement statement = con.createStatement(); String name = "123"; String pwd = "'or'1'='1"; String sql = "select * from tb_user where username = ? and password = ? "; PreparedStatement ps = con.prepareStatement(sql); ResultSet rs =null; ps.setString(1,name); ps.setString(2,pwd); rs = ps.executeQuery();//注意此处不用再传sql语句 ps.setString(1,"name"); ps.setString(2,"pwd"); rs = ps.executeQuery();//注意此处不用再传sql语句 if (rs.next()){ System.out.println("登录成功"); }else { System.out.println("登录失败"); } con.close(); ps.close(); rs.close(); } }
执行后日志的部分变化
2023-01-04T19:00:41.734079Z 6 Prepare select * from tb_user where username = ? and password = ?
2023-01-04T19:00:41.735577Z 6 Execute select * from tb_user where username = '123' and password = '\'or\'1\'=\'1'
2023-01-04T19:00:41.736250Z 6 Execute select * from tb_user where username = 'name' and password = 'pwd'
java代码运行后,mysql会进行sql语法检查,编译sql,这一个步骤很耗时,而预编译,根据日志来看,prepare已经把sql语句编译完了,只等输入?占位符。
在src下创建一个pojo文件夹,用来存放实体类javabean类,例如一些用户账户信息等
ResultSet的代码示例:
package t1; import java.sql.*; public class jdbc { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false"; String username = "root"; String password = "1234"; Connection conn = DriverManager.getConnection(url, username, password); String sql = "select * from account"; Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(sql); while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double money = rs.getDouble(3); //int id = rs.getInt("id"); //String name = rs.getString("name"); //double money = rs.getDouble("money"); System.out.println(id); System.out.println(name); System.out.println(money); System.out.println("------------"); } conn.close(); rs.close(); statement.close(); } }
将数据封装成对象并装入集合,传给前端
package t1; import pojo.Account; import java.sql.*; import java.util.ArrayList; public class jdbc { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false"; String username = "root"; String password = "1234"; Connection conn = DriverManager.getConnection(url, username, password); String sql = "select * from account"; Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(sql); //将数据存入到集合中,用于传到前端。注意要创建pojo文件夹然后创建一个实现类,account用于封装数据 ArrayList<Account> ac = new ArrayList(); while (rs.next()) { Account acc = new Account(); int id = rs.getInt(1); String name = rs.getString(2); double money = rs.getDouble(3); //int id = rs.getInt("id"); //String name = rs.getString("name"); //double money = rs.getDouble("money"); acc.setId(id); acc.setName(name); acc.setMoney(money); ac.add(acc); } System.out.println(ac); conn.close(); rs.close(); statement.close(); } }
sql注入示例:
package t1; import java.sql.*; public class j1 { public static void main(String[] args) throws SQLException { String url = "jdbc:mysql:///db1?useSSL=false"; String username = "root"; String password = "1234"; Connection con = DriverManager.getConnection(url, username, password); Statement statement = con.createStatement(); String name = "123"; String pwd = "'or'1'='1"; String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"' "; ResultSet rs = statement.executeQuery(sql); if (rs.next()){ System.out.println("登录成功"); }else { System.out.println("登录失败"); } con.close(); statement.close(); rs.close(); } }
防止sql注入:
package t1; import java.sql.*; public class j1 { public static void main(String[] args) throws SQLException { String url = "jdbc:mysql:///db1?useSSL=false"; String username = "root"; String password = "1234"; Connection con = DriverManager.getConnection(url, username, password); //Statement statement = con.createStatement(); String name = "123"; String pwd = "'or'1'='1"; String sql = "select * from tb_user where username = ? and password = ? "; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1,name); ps.setString(2,pwd); ResultSet rs = ps.executeQuery();//注意此处不用再传sql语句 if (rs.next()){ System.out.println("登录成功"); }else { System.out.println("登录失败"); } con.close(); ps.close(); rs.close(); } }
数据库连接池:
数据库连接池类似于线程池,当有多个用户来访问数据库时,不用再次创建连接,如果有就可以直接使用,超出部分进行等待,当超过一定的等待时常,会自动进行判断之前的连接占用最长的,进行强制资源释放,给下一个用户
好处:1.资源重用,2.提升系统响应速度,3.避免数据库连接遗漏
Driud德鲁伊数据库连接池(属于阿里巴巴产品,可以在阿里云中查看)
1.导入jar包,粘贴properties
2.代码输入
Properties p = new Properties(); p.load(new FileInputStream("JDBC/src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(p); Connection connection = dataSource.getConnection(); System.out.println(connection);
快捷键:alt+鼠标左键全选,可以同时整列进行编辑
在实体类中,建议使用基本数据类型对应的包装类
注意点:在查询时是调用resultSet的方法getString(int a),在设置时是调用preparedStatement的方法setString(int a,String b)
增删改查:
package t1; import com.alibaba.druid.pool.DruidDataSourceFactory; import org.junit.Test; import pojo.Brand; import javax.sql.DataSource; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Properties; public class t3 { @Test public void query() throws Exception { Properties prop = new Properties(); prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection connection = dataSource.getConnection(); String sql = "select * from tb_brand"; PreparedStatement p = connection.prepareStatement(sql); ResultSet rs = p.executeQuery(); ArrayList<Brand> bs = new ArrayList<>(); while (rs.next()){ Brand b = new Brand(); int id = rs.getInt(1); String brandName = rs.getString(2); String companyName = rs.getString(3); int ordered = rs.getInt(4); String description = rs.getString(5); int status = rs.getInt(6); b.setId(id); b.setBrandName(brandName); b.setCompanyName(companyName); b.setOrdered(ordered); b.setDescription(description); b.setStatus(status); bs.add(b); } System.out.println(bs); connection.close(); p.close(); rs.close(); } @Test public void add() throws Exception { Properties prop = new Properties(); prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection connection = dataSource.getConnection(); String sql = "insert into tb_brand (brand_name,company_name,ordered,description,status) values (?,?,?,?,?)"; PreparedStatement p = connection.prepareStatement(sql); String br = "huang"; String c = "sss"; int o = 123; String d = "qq"; int s = 0; p.setString(1,br); p.setString(2,c); p.setInt(3,o); p.setString(4,d); p.setInt(5,s); int rs = p.executeUpdate(); if (rs>0){ System.out.println("执行成功"); }else{ System.out.println("执行失败"); } connection.close(); p.close(); } @Test public void updata() throws Exception { Properties prop = new Properties(); prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection connection = dataSource.getConnection(); String sql = "update tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where id =?"; PreparedStatement p = connection.prepareStatement(sql); int id = 4; String br = "香喷喷"; String c = "sss"; int o = 123; String d = "qq"; int s = 0; p.setString(1,br); p.setString(2,c); p.setInt(3,o); p.setString(4,d); p.setInt(5,s); p.setInt(6,id); int rs = p.executeUpdate(); if (rs>0){ System.out.println("执行成功"); }else{ System.out.println("执行失败"); } connection.close(); p.close(); } @Test public void delete() throws Exception { Properties prop = new Properties(); prop.load(new FileInputStream("D:/Development/CordData/Project1/JDBC/src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection connection = dataSource.getConnection(); String sql = "delete from tb_brand where id =?"; PreparedStatement p = connection.prepareStatement(sql); int id = 4; p.setInt(1,id); int rs = p.executeUpdate(); if (rs>0){ System.out.println("执行成功"); }else{ System.out.println("执行失败"); } connection.close(); p.close(); } }