JDBC(重点)
数据库驱动
JDBC
SUN公司为了简化 开发人员的(对数据库的同一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说,我们只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包
第一个JDBC程序
-
创建普通项目
-
导入数据库驱动
-
编写代码
package com.gs.lesson; import com.mysql.jdbc.Driver; import java.sql.*; /** * @version: java version 1.8 * @author: 14 * @description: */ public class JdbcFisrstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { // 1、加载驱动 Class.forName("com.mysql.jdbc.Driver"); // 2、用户信息和url String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false"; String username = "root"; String password = "123456"; // 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("id:" + resultSet.getObject("id")); System.out.println("name:" + resultSet.getObject("name")); System.out.println("pwd:" + resultSet.getObject("password")); System.out.println("email:" + resultSet.getObject("email")); System.out.println("birth:" + resultSet.getObject("birthday")); } // 6、释放连接 resultSet.close(); statement.close(); connection.close(); } }
步骤总结:
- 加载驱动
- 连接数据库 DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager
//DriverManager.registerDriver(new Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commint();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//mysql --- 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
//oralce --- 1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement | PrepareStatement 执行sql的对象
String sql = "select * from users" //编写sql
statement.executeQuery();//查询操作 返回ResultSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除 都是用这个, 返回一个受影响的行数
ResultSet 查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();// 在不指定列类型的情况下使用
// 如果知道列类型就使用指定类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
遍历,指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个
resultSet.previous();//移动到前一行
resultSet.absolute();//移动到指定行
释放资源
// 6、释放连接
resultSet.close();
statement.close();
connection.close();// 消耗资源,用完关掉
statement对象
代码实现
1、提取工具类
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
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 conn, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
2、编写增删改的方法 executeUpdate
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
stmt = conn.createStatement(); //获得sql的执行对象
String sql = "INSERT INTO users(ID,NAME,PASSWORD,EMAIL,BIRTHDAY)"+
"VALUES(4,'KUANGSHEN','123456','[email protected]','2025-1-1')";
int i = stmt.executeUpdate(sql);
if (i > 0){
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn, stmt, rs);
}
}
}
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
conn = JdbcUtils.getConnection();
stmt = conn.createStatement();
String sql = "delete from users where id=4";
int i = stmt.executeUpdate(sql);
if ( i > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,stmt,null);
}
}
}
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement stmt = null;
try {
conn = JdbcUtils.getConnection();
stmt = conn.createStatement();
String sql = "UPDATE users SET name= 'kuangshen' ,email = '[email protected]' where id = 1";
int i = stmt.executeUpdate(sql);
if (i > 0) {
System.out.println("修改成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn, stmt, null);
}
}
}
3、查询execuQuery
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
stmt = conn.createStatement();
//SQL
String sql = "select * from users where id = 1";
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn, stmt, rs);
}
}
}
SQL注入的问题
sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or
public class SQL注入 {
public static void main(String[] args) {
login("' or'1=1","' or'1=1");
}
public static void login(String username, String password){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
stmt = conn.createStatement();
String sql = "select * from users where name = '"+username+"' and password = '"+password+"'";
rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn, stmt, rs);
}
}
}
PreparedStatement
PreparedStatement 可以防止SQL注入,并且效率更高
1、新增
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
//区别
//使用?占位符 代替参数
String sql = "INSERT INTO users(ID,NAME,PASSWORD,EMAIL,BIRTHDAY)"+
"VALUES(?,?,?,?,?)";
ps = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
ps.setInt(1,6);
ps.setString(2,"shisi");
ps.setString(3,"123456");
ps.setString(4,"[email protected]");
//注意点:sql.Date 数据库 java.sql.Date
// util.Date java new Date().getTime() 获得时间戳
ps.setDate(5, new java.sql.Date(new Date().getTime()));
//执行
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn, ps, rs);
}
}
}
2、删除
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "delete from users where id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 5);
int i = ps.executeUpdate();
if ( i > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,ps,null);
}
}
}
3、更新
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "UPDATE users SET name=? where id =?";
ps = conn.prepareStatement(sql);
ps.setString(1, "kuangshen");
ps.setInt(2, 5);
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("修改成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn, ps, null);
}
}
}
4、查询
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//SQL
String sql = "select * from users where id =?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 6);//传递参数
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn, ps, rs);
}
}
}
5、防止SQL注入
public class SQL注入 {
public static void main(String[] args) {
login("' or'1=1","' or'1=1");
}
public static void login(String username, String password){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符串
// 假设其中存在转义字符,直接忽略,比如说'
String sql = "select * from users where name = ? and password = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, password);
rs = stmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn, stmt, rs);
}
}
}
使用IDEA连接数据库
事务
代码实现
1、关闭自动提交,开启事务conn.setAutoCommit(false);
2、一组事务执行完毕,提交事务
3、可以在catch语句中显式的定义 回滚语句,但默认失败就会回滚
public class 事务 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
con.setAutoCommit(false);// 关闭自动提交,开启事务
String sql = "UPDATE account SET money = money - 100 where name = 'A'";
ps = con.prepareStatement(sql);
ps.executeUpdate();
//int i = 1/0; // 让 事务 不能完成
sql = "UPDATE account SET money = money + 100 where name = 'B'";
ps = con.prepareStatement(sql);
ps.executeUpdate();
con.commit();// 业务完成 提交
}catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(con, ps, rs);
}
}
}
数据库连接池
数据库连接 ---- 执行完毕 ---- 释放
连接 ---- 释放 十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
----- 开门 ------ 业务员:等待 ------ 服务 ------
若 常用连接数:10个
最小连接数:10个
最大连接数:15个 业务最高承载上限
等待超时:100ms
编写连接池,实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池后,在项目开发中就不需要编写连接数据库的代码了
DBCP
需要的jar包
commons-dbcp-1,4.jar commons-pool-1.6.jar
工具类
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式 --->创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接资源
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
//释放连接资源
public static void release(Connection conn, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
配置文件
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
C3P0
需要的jar包
c3p0-0.9.5.5.jar 、mchange-commons-java-0.2.19.jar
工具类
public class JdbcUtils_C3P0 {
private static DataSource dataSource = null;
static {
try {
//创建数据源 工厂模式 --->创建
dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接资源
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
//释放连接资源
public static void release(Connection conn, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
配置文件
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省(默认)-->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写就表示使用的是name是MySQL的配置-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>
结论
无论使用什么数据源,本质还是一样的,DataSource接口不会变,方法也不会变
标签:JDBC,String,rs,sql,null,重点,public,conn From: https://www.cnblogs.com/shisi2001/p/18336578