JDBC(重点)
第一个JDBC程序
一、创建数据库
-- 创建一个数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;二、创建一个表，并插入数据
-- 创建一张users表
create table `users`(
`id` int primary key,
`name` varchar(40),
`password` varchar(40),
`email` varchar(60),
`birthday` DATE
);
insert into users values
(1,'张三','123456','[email protected]','2020-3-25'),
(2,'李四','123456','[email protected]','2021-3-25'),
(3,'王五','123456','[email protected]','2022-3-25');
select * from users;三、创建一个Java项目
-
在项目目录下创建bin，导入MySQL驱动，右击bin选择create Library这个选项点击ok
四、JDBC代码实现
package com.lyh.www;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//我的第一个JDBC程序
public class JdbcDemo01 {
public static void main(String[] args) {
Connection connection;
Statement statement;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.创建连接
//useUnicode=true&编码
// characterEncoding=utf8格式
// &useSSL=true安全
String url="jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password="123456";
//3.连接成功，数据库对象connection 代表数据库
connection = DriverManager.getConnection(url,username,password);
//3.创建命令对象statement执行sql对象
statement= connection.createStatement();
String sql="select * from users";
//4.创建ResultSet对象
//返回的结果集，结果集中封装了我们全部的查询出来的结构
ResultSet resultSet = statement.executeQuery(sql);
//5.获得结果集对象中的字段的值
while (resultSet.next()){
System.out.println("id="+resultSet.getInt("id"));
System.out.println("name="+resultSet.getString("name"));
System.out.println("password="+resultSet.getString("password"));
System.out.println("email="+resultSet.getString("email"));
System.out.println("birthday="+resultSet.getDate("birthday"));
System.out.println("------------------------");
}
//6.释放链接
resultSet.close();
statement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}五、具体过程
-
加载驱动：打开数据库
Class.forName("com.mysql.jdbc.Driver"); -
创建链接：链接数据库需要，链接，用户，密码
url=//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3;
//useUnicode=true&编码 // characterEncoding=utf8格式 // &useSSL=true安全 String url="jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username="root"; String password="123456"; -
连接成功，返回数据库对象connection ->代表数据库
Connection connection = DriverManager.getConnection(url,username,password); -
创建命令对象statement 执行sql语句
Statement statement= connection.createStatement(); String sql="select * from users"; -
创建ResultSet对象：返回结果集，结果中封装我们全部的查询出来的结构
//4.创建ResultSet对象 //返回的结果集，结果集中封装了我们全部的查询出来的结构 ResultSet resultSet = statement.executeQuery(sql); -
遍历获取结果集对象中的字段值
//5.获得结果集对象中的字段的值 while (resultSet.next()){ System.out.println("id="+resultSet.getInt("id")); System.out.println("name="+resultSet.getString("name")); System.out.println("password="+resultSet.getString("password")); System.out.println("email="+resultSet.getString("email")); System.out.println("birthday="+resultSet.getDate("birthday")); System.out.println("------------------------"); } -
释放链接，反向关闭
//6.释放链接 resultSet.close(); statement.close(); connection.close();
六、statement对象
-
JDBC中的statement对象用于向数据库发送SQL语句，想完成低数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
-
statement对象的executeUpdate方法，用于向数据库发送增删改的sql语句，executeUpdate执行完毕后，将会返回一个整数
-
statement中executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果是resultSet对象。
-
工具包，和一个文件
在src下创建一个db.properties文件
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=123456在com.lyh.lesson02创建一个utils
package com.lyh.lesson02.utils; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; 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 { /** * getClassLoader()获得一个加载器 * getResourceAsStream()拿到他的资源 * 返回一个输入流InputStream */ InputStream inputStream = JdbcUtils.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 close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if (resultSet!=null){ resultSet.close(); } if (statement!=null){ statement.close(); } if (connection!=null){ connection.close(); } } } -
CRUD操作-select
package com.lyh.lesson02; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; //查询全部信息 public class TestSelect { public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { //加载驱动 connection=JdbcUtils.getConnection(); statement=connection.createStatement(); String sql="select * from users"; resultSet = statement.executeQuery(sql); //遍历 while (resultSet.next()){ System.out.println("用户id="+resultSet.getInt("id")); System.out.println("用户名="+resultSet.getString("name")); System.out.println("用户密码="+resultSet.getString("password")); System.out.println("用户邮箱="+resultSet.getString("email")); System.out.println("用户生日="+resultSet.getString("birthday")); System.out.println("-----------------------------------------"); } JdbcUtils.close(connection,statement,resultSet); } catch (SQLException e) { e.printStackTrace(); } } } -
CRUD操作-delete
package com.lyh.lesson02; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; //删除数据中一条数据 public class Testdelet { public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { //创建链接 connection = JdbcUtils.getConnection(); statement=connection.createStatement(); String sql="DELETE from users WHERE `name`='李永辉';"; int i = statement.executeUpdate(sql); if (i>0){ System.out.println("删除成功"); } JdbcUtils.close(connection,statement,resultSet); } catch (Exception e) { e.printStackTrace(); } } } -
CRUD操作-insert
package com.lyh.lesson02; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; //给数据库添加信息 public class TestInsert { public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { //加载驱动，获取数据库链接 connection = JdbcUtils.getConnection(); //获取sql对象 statement=connection.createStatement(); String sql="insert into users values(6,'李永辉','123456','[email protected]','2000-6-02');"; int i=statement.executeUpdate(sql); if (i!=0){ System.out.println("添加成功"); } JdbcUtils.close(connection,statement,resultSet); } catch (Exception e) { e.printStackTrace(); } } } -
CRUD操作-update
package com.lyh.lesson02; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; //修改数据库信息 public class TestUpate { public static void main(String[] args) { Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { //创建链接 connection = JdbcUtils.getConnection(); //获取sql对象 statement = connection.createStatement(); //创建sql语句 String sql="update users set name='郭少' where id=5;"; int i=statement.executeUpdate(sql); if (i>0){ System.out.print("修改成功,您修改的数据为："); String sql1="select * from users where id=5"; resultSet= statement.executeQuery(sql1); while (resultSet.next()){ System.out.println("用户id="+resultSet.getInt("id")); System.out.println("用户名="+resultSet.getString("name")); System.out.println("用户密码="+resultSet.getString("password")); System.out.println("用户邮箱="+resultSet.getString("email")); System.out.println("用户生日="+resultSet.getString("birthday")); } } JdbcUtils.close(connection,statement,resultSet); } catch (SQLException e) { e.printStackTrace(); } } }
七、SQL注入
-
代码实现，会有bug，不安全
package com.lyh.lesson02; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQL注入 { public static void main(String[] args) { login(" 'or '1=1","123456"); } public static void login(String username,String password){ Connection connection=null; Statement statement=null; ResultSet resultSet=null; try { //加载驱动 connection= JdbcUtils.getConnection(); statement=connection.createStatement(); //select * from users WHERE `name`='代雅鑫' AND `password`='123456'; String sql="select * from users WHERE `name`='"+username+"' AND `password`='"+password+"'"; resultSet = statement.executeQuery(sql); //遍历 while (resultSet.next()){ System.out.println("用户名="+resultSet.getString("name")); System.out.println("用户密码="+resultSet.getString("password")); System.out.println("用户邮箱="+resultSet.getString("email")); System.out.println("用户生日="+resultSet.getString("birthday")); System.out.println("-----------------------------------------"); } JdbcUtils.close(connection,statement,resultSet); } catch (SQLException e) { e.printStackTrace(); } } }
八、PreparedStatement对象
-
查询
package com.lyh.lesson03; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestSelect { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; ResultSet resultSet=null; try { connection=JdbcUtils.getConnection(); String sql="select * from users";//编写sql //预编译 preparedStatement=connection.prepareStatement(sql); //执行 resultSet = preparedStatement.executeQuery(); //编辑 while (resultSet.next()){ System.out.println(resultSet.getInt("id")); System.out.println(resultSet.getString("name")); System.out.println(resultSet.getString("password")); System.out.println(resultSet.getString("email")); System.out.println(resultSet.getDate("birthday")); System.out.println("----------------------------"); } JdbcUtils.close(connection,preparedStatement,resultSet); } catch (SQLException e) { e.printStackTrace(); } } } -
添加
package com.lyh.lesson03; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.*; public class Testinsert { public static void main(String[] args) { Connection conn=null; PreparedStatement ps=null; try { //启动驱动 conn=JdbcUtils.getConnection(); //SQL String sql="insert into users values(?,?,?,?,?);"; //预编译 ps=conn.prepareStatement(sql); //插入？所对应的值，一个问好对应一个属性参数 ps.setInt(1,5); ps.setString(2,"李永辉"); ps.setString(3,"12345"); ps.setString(4,"1828762815"); ps.setDate(5,new Date(new java.util.Date().getTime())); //执行 int i = ps.executeUpdate(); if (i>0){ System.out.println("添加成功"); } JdbcUtils.close(conn,ps,null); } catch (Exception e) { e.printStackTrace(); } } } -
修改
package com.lyh.lesson03; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestUpdate { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; try { connection=JdbcUtils.getConnection(); String sql="update users set name=? where id=?;"; preparedStatement=connection.prepareStatement(sql); preparedStatement.setString(1,"李永辉"); preparedStatement.setInt(2,5); int i = preparedStatement.executeUpdate(); if (i>0){ System.out.println("修改成功？"); } } catch (Exception e) { e.printStackTrace(); }finally { try { JdbcUtils.close(connection,preparedStatement,null); } catch (SQLException e) { e.printStackTrace(); } } } } -
删除
package com.lyh.lesson03; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestDelete { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; try { //创建驱动和链接 connection=JdbcUtils.getConnection(); String sql="delete from users WHERE `name`=?;"; preparedStatement=connection.prepareStatement(sql); preparedStatement.setString(1,"李永辉"); int i = preparedStatement.executeUpdate(); if (i>0){ System.out.println("删除成功"); } JdbcUtils.close(connection,preparedStatement,null); } catch (SQLException e) { e.printStackTrace(); } } } -
模拟登录页面
package com.lyh.lesson02; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.*; public class SQL注入 { public static void main(String[] args) { login("代雅鑫","123456"); } public static void login(String username,String password){ Connection connection=null; PreparedStatement statement=null; ResultSet resultSet=null; try { //加载驱动 connection= JdbcUtils.getConnection(); String sql="select * from users WHERE `name`=? AND `password`=?"; //prepareStatement 防止SQL注入，把传递进来的参数当作字符串，这样就可以，预防SQL注入 statement=connection.prepareStatement(sql); statement.setString(1,username); statement.setString(2,password); resultSet=statement.executeQuery(); //select * from users WHERE `name`='代雅鑫' AND `password`='123456'; //遍历 while (resultSet.next()){ System.out.println("用户名="+resultSet.getString("name")); System.out.println("用户密码="+resultSet.getString("password")); System.out.println("用户邮箱="+resultSet.getString("email")); System.out.println("用户生日="+resultSet.getString("birthday")); System.out.println("-----------------------------------------"); } JdbcUtils.close(connection,statement,resultSet); } catch (SQLException e) { e.printStackTrace(); } } }
九、事务
-
事务原则
-
原子性：要么成功，要么失败
-
一致性：事务前后的数据完整性要保持一致
-
持久性：事务一点提交则不可逆，被持久化到数据库中
-
隔离性：每个事务操作都会被隔离
-
-
隔离所导致的一些问题
-
脏读：指一个事务读取了另一个事务未提交的数据
-
不可重复读：在一个事务内读取表中的某一行数据，多次读取结构不同
-
虚读：是指在一个事务内读取到别的事务插的数据，导致前后读取不一致
-
-
代码实现
-
开启事务
-
一组业务执行完毕，提交事务
-
可以在catch语句中显示的定义回滚，但默认 失败就会回滚
package com.lyh.lesson04; import com.lyh.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestTransaction2 { public static void main(String[] args) { Connection connection=null; PreparedStatement preparedStatement=null; ResultSet resultSet=null; try { //加载驱动，获取链接 connection=JdbcUtils.getConnection(); //关闭数据库的自动提交，自动会开启事务 connection.setAutoCommit(false);//开启事务 //执行sql语句 String sql="UPDATE account SET money=money-100 where `name`='A';"; preparedStatement=connection.prepareStatement(sql); preparedStatement.executeUpdate(); //错误 int i=1/0; //执行sql语句 String sql1="UPDATE account SET money=money+100 where `name`='B';"; preparedStatement=connection.prepareStatement(sql1); preparedStatement.executeUpdate(); //业务完毕,提交事务 connection.commit(); System.out.println("成功"); } catch (SQLException e) { try { connection.rollback();//如果失败则回滚 } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); }finally { try { //关闭链接，释放资源 JdbcUtils.close(connection,preparedStatement,resultSet); } catch (SQLException e) { e.printStackTrace(); } } } }
-
总结：
1、 简单的JDBC过程
-
加载驱动打开数据库
Class.forName("com.mysql.jdbc.Driver"); -
创建链接，链接数据库需要密码，用户，链接
String url="jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username="root"; String password="123456"; -
连接成功，返回数据库对象connection
Connection connection = DriverManager.getConnection(url,username,password); -
执行sql对象，返回给创建命令对象statement执行sql对象
Statement statement= connection.createStatement(); String sql="select * from users"; -
返回ResultSet结果集，结果集中封装了我们全部查询出来的结果
ResultSet resultSet = statement.executeQuery(sql); -
遍历（如果要是增加，修改，删除不用遍历）
while (resultSet.next()){ //存在ResultSet对象中，get从这个对象中取出来 System.out.println(resultSet.getInt("id")); System.out.println(resultSet.getString("name")); System.out.println(resultSet.getString("password")); System.out.println(resultSet.getString("email")); System.out.println(resultSet.getDate("birthday")); System.out.println("------------------------"); } -
释放资源（反向关闭）
//6.释放链接 resultSet.close(); statement.close(); connection.close();
已见底
标签：总结,jdbc,resultSet,System,connection,sql,import,out From： https://www.cnblogs.com/lyhidea/p/16841584.html