java-jdbc-all
jdbc相关解析
JDBC(Java DataBase Connectivity,Java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成,JDBC提供一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能编写数据库应用程序。
原理简介
SUN提供一套访问数据库的规范(就是一组接口),并提供连接数据库的协议标准,然后各个数据库厂商会遵循SUN的规范提供一套访问自己公司的数据库服务器的API出现。SUN提供的规范命名为JDBC,而各个厂商提供的,遵循了JDBC规范的,可以访问自己数据库的API被称之为驱动!
因此各个厂商做的驱动就是对于sun公司jdbcAPI的接口的实现类,所以必须要有驱动才能链接到数据库
JDBC的流程 :
111驱动器的加载
222数据库的连接
333创建执行对象
444执行sQL语句
555关闭资源
JDBC核心接口简介
DriverManager、Connection、Statement、ResultSet。
111DriverManager类
驱动管理器,是管理一组JDBC驱动程序的基本服务,主要是用于 注册驱动 和 获取连接。
注册驱动:让JDBC接口知道连接的是哪个驱动(也就是连接哪个数据库)
Class.forName("com.mysql.jdbc.Driver"); //Mysql Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Class.forName作用
因为这个主要是将类加载到内存当中
使用这个方法时,Driver类的静态代码块会向内存注册入戏
获取连接对象
通过DriverManager的api来获取连接对象。
Connection DriverManager.getConnection(url,username,password);
参数的含义:
url:用于标识数据库的位置,要连接的是什么数据库。
url格式:协议:子协议:子协议名称:IP地址:端口号:数据库名称(前部分是数据库厂商规定的)
username:是我们要连接的数据库的登陆名
password:使我们要登陆用户的对应密码(如果没设置可以为空)
常用URL地址的写法:
MySql:jdbc:mysql://localhost:3306/数据库名称 Oracle:jdbc:oracle:thin:@localhost:1521/数据库名称 SqlServer:jdbc:microsoft:sqlserver://localhost:1433/数据库名称
2222、Connection 接口:
如果可以获取到Connection对象,那么说明已经与数据库连接上了,Connection对象表示连接,与数据库的通讯录都是通过这个对象展开的:
Connection最重要的一个方法就是用来获取Statement对象和PreparedStatement对象;
创建Statement -语句执行者
Statement st = null; //获取用于向数据库发送sql语句的statement st = conn.createStatement();
创建一个预编译的语句执行对象:
PreperedStatement st = null; String sql = "select * from users where username=? and password=?"; //获取用于向数据库发送sql语句的Preperedstatement st = conn.preparedStatement(sql);//在此次传入,进行预编译
创建一个 CallableStatement 对象来调用数据库存储过程
CallableStatement prepareCall(String sql);
Statement接口:
Statement对象是SQL语句的执行者,是用来向数据库发送SQL语句的。
执行查询语句,返回一个集合:ResultSet executeQuery(String sql) 执行更新 插入 删除语句,返回影响行数:int executeUpdate(String sql) 把多条sql语句放到一个批处理中:addBatch(String sql),然后通过executeBatch()向数据库发送一批sql语句执行。 执行给定的 SQL 语句,该语句可能返回多个结果:boolean execute(sql)
boolean execute使用注意:
注:该方法返回值不同,使用方式也不同:返回值为true时,表示执行的查询语句,使用getResultSet方法获取结果;返回值为false时,表示执行更新或DDL语句,使用getUpdateCount获取结果。
ResultSet接口:
ResultSet用于代表Sql语句的执行结果。Resultset封装执行结果时,采用的类似于表格的方式,ResultSet 对象维护了一个指针,初始的时候,指针指向的是结果集的第一行,可以通过提供的api对指针进行操作,查看结果集。
ResultSet对象对指针操作的方法:
next():移动到下一行 Previous():移动到前一行 absolute(int row):移动到指定行 beforeFirst():移动resultSet的最前面 afterLast() :移动到resultSet的最后面
直接获取值的方法:
获取任意类型的数据: getObject(int index) getObject(string columnName) 获取指定类型的数据,例如: getString(int index) getString(String columnName)
statement 和PreperedStatement的区别
关系:PreparedStatement继承自Statement,都是接口
区别:PreparedStatement可以使用占位符,是预编译的,批处理比Statement效率高
SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。
继承关系
作为 Statement 的子类,PreparedStatement 继承了 Statement 的所有功能,
Statement对象能做的操作Preparedstatement都能做,Preparedstatement能做的Statement不一定能做
安全性:
SQL注入:
String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'"; 我们把[' or '1' = '1]作为varpasswd传入进来.就会变成: select * from tb_name = '随意' and passwd = '' or '1' = '1'; 因为'1'='1'肯定成立,所以可以任何通过验证.更有甚者 再或者:把[';drop table tb_name;]作为varpasswd传入进来 得到: select * from tb_name = '随意' and passwd = '';drop table tb_name;
而因为
PreparedStatement是实现占位符填充的
String param = "'test' or 1=1"; String sql = "select file from file where name = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, param); 得到的SQL语句是: select file from file where name = '\'test\' or 1=1'
sql的执行函数已经确定,所以不会再破坏sql的结构。所以可以防止sql注入。
PreparedStatement具备预编译功能,其首先对sql语句进行预编译,然后再将值填充到对应占位符处
主要用的步骤格式如下;
perstmt = con.prepareStatement("insert into tb_name (col1,col2,col2,col4) values (?,?,?,?)"); perstmt.setString(1,var1); perstmt.setString(2,var2); perstmt.setString(3,var3); perstmt.setString(4,var4); perstmt.executeUpdate(); //prestmt是 PreparedStatement 对象实例
整体的思路:
111因为每次都需要 建立连接,我们可以将连接作为一个累,同一接口就行:
222进行增删改查
注意:这里使用的是maven项目,因为比较好管理依赖包:
新建maven项目:加入依赖包:
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.49</version> </dependency> <!--数据库连接池--> <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.2.0</version> </dependency>
建立获取连接的累:
import java.io.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class JdbcConnection { /* 关于数据库连接所需要的东西我们可以在这里直接赋值,也可以使用高级的做法,写配置文件jdbc.properties private static final String driver = "com.mysql.jdbc.Driver"; private static final String url = "jdbc:mysql://129.204.3.133:3306/students"; private static final String dbusername = "root"; private static final String dbpassword = "2004"; */ //从jdbc.properties获取变量值 private static String driver; private static String url; private static String dbusername; private static String dbpassword; //利用静态代码块,优先获取属性的值: static { //创建java中的属性集Properties Properties prop = new Properties(); //直接使用文件路径获得文件输入流 // FileInputStream ips = new FileInputStream("src\\main\\resources\\jdbc.properties"); // 利用class自带的资源加载方法进行文件输入流的获取 //解析:https://blog.csdn.net/liu911025/article/details/80415001 InputStream ips=JdbcConnection.class.getClassLoader() .getResourceAsStream("jdbc.properties"); try { prop.load(ips); driver =prop.getProperty("driver"); url = prop.getProperty("url"); dbusername = prop.getProperty("dbusername"); dbpassword = prop.getProperty("dbpassword"); } catch (IOException e) { e.printStackTrace(); }finally { try { ips.close();//关闭输入流 } catch (IOException e) { e.printStackTrace(); } } } //获取连接 public static Connection getCon() throws ClassNotFoundException, SQLException { //注册使用哪个驱动 Class.forName(driver); //创建新的连接 Connection connection= DriverManager.getConnection(url,dbusername,dbpassword); return connection; } }
再resources目录下新建一个配置文件:jdbc.properties
用于数据源的配置:
driver=com.mysql.jdbc.Driver url=jdbc:mysql://129.204.3.133:3306/students dbusername=root dbpassword=2004
实现查询
import connector.JdbcConnection; import java.sql.*; public class SelectJdbc { public static void main(String[] args) throws SQLException, ClassNotFoundException { select(); } public static void select() { try{ Connection con = JdbcConnection.getCon(); Statement stat = con.createStatement(); String selectSql = "select * from weibo_user"; ResultSet resultSet = stat.executeQuery(selectSql); int i = 0; while (resultSet.next()){ //从结果集当中获取username的值,因为username对应的值类型是String,所以 //使用getString ,括号是表的列名称 String name = resultSet.getString("username"); Date date = resultSet.getDate("create_at"); System.out.print(name+" "); System.out.println(date); i++; if (i >=10){//控制输出10个数据 break; } } //关闭stat stat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }
实现增加:
import connector.JdbcConnection; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class InsertJdbc { public static void main(String[] args) throws SQLException, ClassNotFoundException { Insert(); } private static void Insert() throws SQLException, ClassNotFoundException { Connection con = JdbcConnection.getCon(); Statement stat = con.createStatement(); String insertsql = "INSERT INTO weibo_user(username,password,nickname,status,create_at,remark) VALUES('06zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')"; int how = stat.executeUpdate(insertsql);//int返回影响的行数 // boolean how = stat.execute(insertsql); /* true if the first result is a ResultSet object; false if it is an update count or there are no results */ System.out.println("insert"+how); stat.close(); } }
实现更改:
import connector.JdbcConnection; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class UpdateJdbc { public static void main(String[] args) { update(); } private static void update() { try{ Connection con = JdbcConnection.getCon(); Statement stat = con.createStatement(); String updatesql = "UPDATE weibo_user SET username='quanupdate' WHERE id = 666669 "; int how =stat.executeUpdate(updatesql); System.out.println(how); } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }
实现删除:
import connector.JdbcConnection; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class DeleteJdbc { public static void main(String[] args) throws SQLException, ClassNotFoundException { delete(); } private static void delete() throws SQLException, ClassNotFoundException { Connection con = JdbcConnection.getCon(); Statement stat = con.createStatement(); String deletesql = "DELETE FROM weibo_user WHERE id =666668"; int how = stat.executeUpdate(deletesql); System.out.println(how); } }
多条语句一起执行addbach方法
import connector.JdbcConnection; import java.sql.*; public class MoreInsertJdbc { public static void main(String[] args) { // moreInset(); moreInsertP(); } //使用Statement对象完成多条执行 public static void moreInset(){ try{ Connection con = JdbcConnection.getCon(); Statement stat = con.createStatement(); String insertsql1 = "INSERT INTO weibo_user(username,password,nickname,status,create_at,remark) " + "VALUES('0611zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')"; String insertsql2 = "INSERT INTO weibo_user(username,password,nickname,status,create_at,remark) " + "VALUES('0622zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')"; String insertsql3 = "INSERT INTO weibo_user(username,password,nickname,status,create_at,remark) " + "VALUES('0633zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')"; stat.addBatch(insertsql1); stat.addBatch(insertsql2); stat.addBatch(insertsql3); //执行batch int[] how = stat.executeBatch(); for (int i : how) { System.out.println(i); } /* 1 1 1 */ } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /* 上面的太复杂了,每次的SQL语句都要写一遍,所以我们使用另外一个执行器PrepareStatement对象 */ public static void moreInsertP(){ try{ Connection con = JdbcConnection.getCon(); String insertp = "INSERT INTO weibo_user(username,password,nickname,status,create_at,remark) " + "VALUES(?,?,?,?,?,?)"; PreparedStatement pstat = con.prepareStatement(insertp); //第一个数据 pstat.setString(1,"0611zhangsann"); pstat.setString(2, "1111"); pstat.setString(3,"unickname"); pstat.setInt(4 ,2); pstat.setString(5, "2020-05-05 10:24:24"); pstat.setInt(6,22); pstat.addBatch(); //第二个数据 pstat.setString(1,"0622zhangsann"); pstat.setString(2, "1111"); pstat.setString(3,"unickname"); pstat.setInt(4 ,2); pstat.setString(5, "2020-05-05 10:24:24"); pstat.setInt(6,22); pstat.addBatch(); //好像也没有这么简单 //执行: int[] how =pstat.executeBatch(); for (int i : how) { System.out.println(i); } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }
关于事务,jdbc也是支持的:
在开发中,我们对数据库的多个表或对一个表的多条数据执行更新操作的时候,要保证多个更新操作要么同时成功、要么都不成功。这就涉及到多个更新操作的事务管理问题了
例子:
银行的转账问题,A用户向B用户转账100元,假设A用户和B用户的钱都存储在Account表中,那么A向B转账就涉及同时更新Account表中的A用户的钱和B用户的钱,不然的话,A的钱少了,而B却没有收到钱,这是不允许出现的事件。
update account set money = money -100 where name = 'A';
update account set money = money + 100 where name = 'B';
事务能够控制何时更改提交并应用于数据库。它将单个SQL语句或一组SQL语句视为一个逻辑单元,如果任何语句失败,整个事务将失败。
注意,
JDBC连接默认是处于自动提交,我们需要手东的关闭自动提交
conn.setAutoCommit(false);
所有操作无误执行后进行提交:
con.commit();
否则回滚;
con.rollback();
更高级的用法:
Savepoint
对象,主要是建立快照,方便回滚到指定地点:
setSavepoint(String savepointName);//定义新的保存点,返回`Savepoint`对象。 releaseSavepoint(Savepoint savepointName);//删除保存点。参数是由上面的方法生出的对象。
一旦设立指定快照点后,救可以回滚
使用rollback(String savepointName)方法,就可以将事务回滚到指定的保存点
import connector.JdbcConnection; import java.sql.Connection; import java.sql.SQLException; import java.sql.Savepoint; import java.sql.Statement; public class AboutCommit { public static void main(String[] args) { } public static void Com() throws SQLException, ClassNotFoundException { Connection con = JdbcConnection.getCon(); Savepoint sp = null; //注意变量的域范围 try { con.setAutoCommit(false); sp = con.setSavepoint("firstPoint"); Statement stat = con.createStatement(); String sql = "INSERT INTO weibo_user(username,password,nickname,status,create_at,remark) VALUES('06zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')"; int how = stat.executeUpdate(sql); //因为JDBC默认是自动提交的,把自动提交关闭 con.commit(); } catch (SQLException throwables) { con.rollback(sp);//回滚 } } }
jdbc还提供获取数据库和表元数据的累:
import connector.JdbcConnection; import java.sql.*; public class MetaDatabase { public static void main(String[] args) { getMeta(); } private static void getMeta(){ try{ Connection con = JdbcConnection.getCon(); //数据库元数据 DatabaseMetaData dbMeta = con.getMetaData(); System.out.println("数据库请求地址:"+dbMeta.getURL()); System.out.println("数据库系统函数:"+ dbMeta.getSystemFunctions()); System.out.println("登录数据库的用户:"+dbMeta.getUserName()); System.out.println("数据库名i在:"+dbMeta.getDatabaseProductName()); System.out.println("驱动版本:"+dbMeta.getDriverVersion()); //数据表元数据 Statement stat = con.createStatement(); String sql ="SELECT * FROM weibo_user"; ResultSet rset = stat.executeQuery(sql); ResultSetMetaData rdate = rset.getMetaData(); int count = rdate.getColumnCount(); System.out.println("表字段总数:"+count); for (int i=1;i<=count;i++){ System.out.println("第"+i+"列的字段名:"+rdate.getColumnLabel(i)+" 类型:"+rdate.getColumnTypeName(i)); } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }
结果:
数据库请求地址:jdbc:mysql://129.204.3.133:3306/students 数据库系统函数:DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,LAST_INSERT_ID,VERSION 登录数据库的用户:root@163.125.239.90 数据库名i在:MySQL 驱动版本:mysql-connector-java-5.1.49 ( Revision: ad86f36e100e104cd926c6b81c8cab9565750116 ) 表字段总数:7 第1列的字段名:id 类型:INT 第2列的字段名:username 类型:VARCHAR 第3列的字段名:password 类型:VARCHAR 第4列的字段名:nickname 类型:VARCHAR 第5列的字段名:status 类型:SMALLINT 第6列的字段名:create_at 类型:DATETIME 第7列的字段名:remark 类型:VARCHAR
同时提供获取主键的累:
import connector.JdbcConnection; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /* 获取表中的主键值 */ public class GetGeneratedKeys { public static void main(String[] args) { getKeys(); } public static void getKeys(){ try{ Connection con = JdbcConnection.getCon(); Statement stat = con.createStatement(); //查询语句是获取不了主键值的 // String sql = "select * from weibo_user"; // stat.executeQuery(sql,Statement.RETURN_GENERATED_KEYS); String insertsql = "INSERT INTO weibo_user(username,password,nickname,status,create_at,remark) VALUES('0655zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')"; stat.executeUpdate(insertsql,Statement.RETURN_GENERATED_KEYS); ResultSet rset = stat.getGeneratedKeys(); while (rset.next()){ int id = rset.getInt(1); /* int id = rset.getInt(2);因为组件返回的结果集只有一个,所以2就会报错,数字是指,index索引 java.sql.SQLException: Column Index out of range, 2 > 1. */ System.out.println(id); } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }
但是每一个SQL操作都创建一个连接,消耗的系统资源比较大,这时候就利用到了连接池
关于连接池的介绍可以看看
https://www.cnblogs.com/java-quan/p/13190779.html
第一个连接池:dbcp
import connector.JdbcConnection; import org.apache.commons.dbcp2.BasicDataSource; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; public class JdbcConnectionSource { private static String driver; private static String url; private static String dbusername; private static String dbpassword; private static BasicDataSource dataSource; static { Properties prop = new Properties(); InputStream ips = JdbcConnectionSource.class.getClassLoader() .getResourceAsStream("jdbc.properties"); try { prop.load(ips); driver = prop.getProperty("driver"); url = prop.getProperty("url"); dbusername = prop.getProperty("dbusername"); dbpassword = prop.getProperty("dbpassword"); //设置datasource dataSource = new BasicDataSource(); dataSource.setUrl(url); dataSource.setDriverClassName(driver); dataSource.setUsername(dbusername); dataSource.setPassword(dbpassword); //初始化datasouce参数设置 dataSource.setInitialSize(3); dataSource.setMaxTotal(5); } catch (IOException e) { e.printStackTrace(); } finally { try { ips.close();//关闭输入流 } catch (IOException e) { e.printStackTrace(); } } } public static Connection getCon() throws SQLException { Connection connection = dataSource.getConnection(); return connection; } }
之后也是直接使用getCon方法进行连接的获取。
第二个是阿里云的druid连接池:
需要引入依赖包:
<!--上面的dbcp连接池性能不好,用阿里云的 https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.22</version> </dependency>
使用连接池;
import com.alibaba.druid.pool.DruidDataSource; import java.io.InputStream; import java.util.Properties; public class JdbcDruidSource { private static String driver; private static String url; private static String dbusername; private static String dbpassword; private static DruidDataSource dataSource; static { try{ Properties prop = new Properties(); InputStream ips = JdbcDruidSource.class.getClassLoader() .getResourceAsStream("jdbc.properties"); dataSource = new DruidDataSource(); dataSource.setUrl(url); dataSource.setUsername(dbusername); dataSource.setPassword(dbpassword); }catch (Exception e){ e.printStackTrace(); } } }