第一个程序
import java.sql.*;
public class Demo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.用户信息和url
String url = "jdbc:mysql://localhost:3306/user";
String username = "root";
String password = "root";
//3.连接成功,数据库对象
Connection connection=DriverManager.getConnection(url,username,password);
//4.执行sql语句
Statement statement=connection.createStatement();
//5.执行sql的对象 去 执行sql,可能存在结果,查看返回结果
String sql="select * from users" ;
ResultSet resultSet=statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("time"));
System.out.println(resultSet.getObject("point"));
System.out.println(resultSet.getObject("sex"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结
-
记载驱动
-
连接数据库 DriverManage
-
获取执行的sql对象 Statement
-
获取返回的结果集
-
释放连接
对象
DriverManager
Class.forName("com.mysql.cj.jdbc.Driver")//加载驱动
Connection connection= DriverManager.getConnection(url,usename,password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//自动回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url="jdbc:mysql://localhost:3306/user";
//mysql --- 3306
//协议://主机地址:端口号/数据库名?参数1&参数2&参数3
Statement/PrepareStatement执行SQL对象
String sql="select * from users"; //编写sql
statement.executeQuery(); //查询操作返回ResultSet
statement.execute(); //执行到SQL
statement.executeUpdate(); //更新,插入,删除。都是用这个 ,返回一个受影响的行数
ResultSet:查询的结果集:封装了所有的查询结果
resultSet.getObject( );获取数据类型
遍历,指针
resultSet.beforeFirst(); //移动到最前面
resultSet.aferLast(); //移动到最后面
resultSet.next(); //移动到下一行数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();
代码实现
-
提取工具类
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=root
import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JDBCUtil { private static String driver=null; private static String url = null; private static String username = null; private static String password = null; static { try { InputStream inputStream=JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(inputStream); driver = properties.getProperty("driver"); url=properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1.驱动只用加载一次 Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } //释放连接资源 public static void release(Connection connection, Statement statement, ResultSet resultSet){ if (resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement!=null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
-
编写增删改查的方法 executeUpdate
增
import com.imnu.Day02.Util.JDBCUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class InsertTest{ public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { connection= JDBCUtil.getConnection(); //获取连接 statement=connection.createStatement(); String sql="insert into users(name,time,point,sex)values('zhangsan','2001.03.14 20:01:02',100,'nan')" ; statement=connection.prepareStatement(sql); //获取SQL的执行对象 int i=statement.executeUpdate(sql); if (i>0){ System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection, statement,resultSet); } } }
删
import com.imnu.Day02.Util.JDBCUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DeleteTest { public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { connection= JDBCUtil.getConnection(); //获取连接 statement=connection.createStatement(); String sql="delete from users where id = 4" ; statement=connection.prepareStatement(sql); //获取SQL的执行对象 int i=statement.executeUpdate(sql); if (i>0){ System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection, statement,resultSet); } } }
改
import com.imnu.Day02.Util.JDBCUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class UpdateTest { public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { connection= JDBCUtil.getConnection(); //获取连接 statement=connection.createStatement(); String sql="update users set name ='王五' where id =1" ; statement=connection.prepareStatement(sql); //获取SQL的执行对象 int i=statement.executeUpdate(sql); if (i>0){ System.out.println("更新成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection, statement,resultSet); } } }
查 executeQuery
import com.imnu.Day02.Util.JDBCUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SelectTest { public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { connection = JDBCUtil.getConnection(); statement=connection.createStatement(); String sql="select * from users where id"; resultSet=statement.executeQuery(sql); while (resultSet.next()){ System.out.println(resultSet.getString("name")); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection, statement, resultSet); } } }
PreparedStatement对象
PreparedStatement对象可以防止SQL注入,效率高
-
增
import com.imnu.Day02.Util.JDBCUtil; import java.sql.*; public class InsertTest02 { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; try { connection= JDBCUtil.getConnection(); String sql="insert into user01 (id,name ,password) values (?,?,?)"; preparedStatement=connection.prepareStatement(sql); //手动给参数赋值 preparedStatement.setInt(1,4); preparedStatement.setString(2,"lli"); preparedStatement.setString(3,"sssss"); //执行 int i=preparedStatement.executeUpdate(); if (i>0){ System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection,preparedStatement,null); } } }
2.删
import com.imnu.Day02.Util.JDBCUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class DeleteTest02 { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; try { connection= JDBCUtil.getConnection(); String sql="delete from user01 where id=? "; preparedStatement=connection.prepareStatement(sql); preparedStatement.setInt(1,1); //执行 int i=preparedStatement.executeUpdate(); if (i>0){ System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection,preparedStatement,null); } } }
3.改
import com.imnu.Day02.Util.JDBCUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class UpdateTest02 { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; try { connection= JDBCUtil.getConnection(); String sql="update user01 set `name` = ? where id = ?"; preparedStatement=connection.prepareStatement(sql); preparedStatement.setString(1,"张"); preparedStatement.setInt(2,2); //执行 int i=preparedStatement.executeUpdate(); if (i>0){ System.out.println("更新成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection,preparedStatement,null); } } }
4.查
import com.imnu.Day02.Util.JDBCUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class SelectTest02 { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; ResultSet resultSet=null; try { connection= JDBCUtil.getConnection(); String sql="select * from user01 where id = ? "; preparedStatement=connection.prepareStatement(sql); preparedStatement.setInt(1,2); //执行 resultSet=preparedStatement.executeQuery(); if (resultSet.next()){ System.out.println(resultSet.getString("name")); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection,preparedStatement,null); } } }
5.防止SQL注入
import java.sql.*; public class SQLUtil { public static void main(String[] args) { login("张","root"); } public static void login(String username,String pasword){ Connection connection=null; PreparedStatement preparedStatement=null; ResultSet resultSet=null; try { connection=JDBCUtil.getConnection(); String sql="select * from user01 where `name`=? and password =? "; preparedStatement=connection.prepareStatement(sql); preparedStatement.setString(1,username); preparedStatement.setString(2,pasword); resultSet=preparedStatement.executeQuery(); while (resultSet.next()){ System.out.println(resultSet.getString("name")); System.out.println(resultSet.getString("password")); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtil.release(connection, preparedStatement, resultSet); } } }
(Other)
properties(类)
Properties可以用来保存属性集(类似Map, 以键值的形式保存数据,不同的是Properties都是String类型的)。这个类的优势是可以从流中加载属性集,或者把属性集报错到流中。
基本使用
public Object setProperty(String key, String value); // 新增或修改一个属性 public String getProperty(String key); // 获得key对应的值 public Set<String> stringPropertyNames(); // 所有key的名称的集合
使用方法
public static void main(String[] args) { Properties properties = new Properties(); properties.setProperty("name", "xiaoming"); properties.setProperty("age", "18"); properties.setProperty("sex", "女"); properties.stringPropertyNames().forEach(e ->{ System.out.println(e + " = " + properties.get(e)); }); }
从流中读写Properties
写Properties到流
public static void main(String[] args) { Properties properties = new Properties(); properties.setProperty("name", "xiaoming"); properties.setProperty("age", "18"); properties.setProperty("sex", "女"); properties.stringPropertyNames().forEach(e ->{ System.out.println(e + " = " + properties.get(e)); }); try (Writer writer = new FileWriter(new File("properties.txt"));) { properties.store(writer, "afdsafdsa"); } catch (IOException e) { e.printStackTrace(); } }
打开properties.txt,内容如下:
#afdsafdsa #Wed Oct 14 13:04:35 CST 2020 sex=女 name=xiaoming age=18
从流中读取Properties
public static void main(String[] args) { Properties properties = new Properties(); try (Reader reader = new FileReader(new File("properties.txt"))) { properties.load(reader); properties.stringPropertyNames().forEach(e ->{ System.out.println(e + " = " + properties.get(e)); }); } catch (IOException e) { e.printStackTrace(); } }
事务
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库
隔离性问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
虚度(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果前后不一致
代码实现
- 开启事务 connection.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在catch语句中显示的定义 回滚语句,但默认失败就会 回滚
public class JdbcDemo10 {
//事务操作
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement1=null;
PreparedStatement preparedStatement2 =null;
try {
connection = Jdbcutils.getConnection();
//开启事务
connection.setAutoCommit(false);
//张三-500
String sql1="update account set balance=balance-? where id=?";
//李四加500
String sql2="update account set balance=balance+? where id=?";
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement2 = connection.prepareStatement(sql2);
preparedStatement1.setDouble(1,500);
preparedStatement1.setInt(2,1);
preparedStatement2.setDouble(1,500);
preparedStatement2.setInt(2,2);
preparedStatement1.executeUpdate();
//制造异常
int i=3/0;
preparedStatement2.executeUpdate();
//提交事务
connection.commit();
} catch (SQLException throwables) {
//事务回滚
try {
if(connection!=null)
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
Jdbcutils.close(null,preparedStatement1,connection);
Jdbcutils.close(null,preparedStatement2,null);
}
}
}
数据库连接池
数据库连接—执行完毕—释放
连接—释放 十分浪费资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
编写连接池,实现一个接口 DataSource
开源数据源实现 (拿来即用)
DBCP
C3P0
Druid:阿里巴巴
使用数据库连接池之后,我们在项目开发中就不需要编写连接数据库代码了!
DBCP
需要用到的jar包
commons-dbcp-1.4,commons-pool-1.6
#连接设置 driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=root # initialSize=10 #最大连接数量 maxActive=50 # maxIdle=20 # minIdle=5 # maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;] #注意:“user” 与 “password” 两个属性会被明确地传递,因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交(auto-commit)状态。 defaultAutoCommit=true defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。 #可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
工具包
package com.imnu.Day03.Util; import org.apache.commons.dbcp.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JDBCUtil_DBCP { private static DataSource dataSource=null; static { try { InputStream inputStream= JDBCUtil_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties properties = new Properties(); properties.load(inputStream); //创建数据源 工厂模式-->创建对象 dataSource = BasicDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); //从数据源中获取连接 } //释放连接资源 public static void release(Connection connection, Statement statement, ResultSet resultSet){ if (resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement!=null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
Test包
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class Test_DBCP { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; try { connection= JDBCUtil_DBCP.getConnection(); String sql="insert into user01 (id,name ,password) values (?,?,?)"; preparedStatement=connection.prepareStatement(sql); //手动给参数赋值 preparedStatement.setInt(1,7); preparedStatement.setString(2,"llis"); preparedStatement.setString(3,"sssss"); //执行 int i=preparedStatement.executeUpdate(); if (i>0){ System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil_DBCP.release(connection,preparedStatement, null); } } }
C3P0
需要导入的jar包
c3p0-0.9.5.5,mchange-commons-java-0.2.3.4
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf8&useSSL=true</property> <property name="user">root</property> <property name="password">root</property> <property name="initialPoolSize">5</property> <property name="maxIdleTime">10</property> <property name="maxPoolSize">5</property> <property name="minPoolSize">20</property> </default-config> <named-config name="mySQL"> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/user</property> <property name="user">root</property> <property name="password">root</property> <property name="initialPoolSize">5</property> <property name="maxIdleTime">10</property> <property name="maxPoolSize">5</property> <property name="minPoolSize">20</property> </named-config> </c3p0-config>
结论
无论使用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变