JDBC
- sun公司定义的接口，各个厂商需要自己实现这个接口，提供数据库驱动jar包，真正执行代码的是驱动jar包中的实现类
步骤：
-
导入jar包
-
注册驱动
-
获取数据库链接对象
-
定义sql
-
获取执行对象的statement
-
执行sql，接收返回结果
-
处理结果
-
释放资源
public static void main(String[] args) throws Exception { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //获取数据库连接对象 Connection connection= DriverManager.getConnection("jdbc；mysql://3306/数据库名","用户名","密码"); //定义sql语句 String sql="update account set balance = 1000"; //获取sql的对象 Statement statement=connection.createStatement(); //执行sql语句 int count=statement.executeUpdate(sql); //处理结果 System.out.println(count); //释放资源 statement.close(); connection.close();
1. DriverManager:驱动管理对象
注册驱动
-
思考:为什么注册的时候用的是"com.mysql.jdbc.Driver"？
- 因为在数据库连接驱动的jar包目录下com.mysql.jdbc.Driver中，有一个static代码块，随着jar包的加载而运行，这个static代码块是java.sql.DriverManager.registerDriver，用来进行注册驱动(mysql 5.x版本之后免注册驱动，因为jar目录下已经有配置文件写入了)
获取数据库连接：
- 方法：static Connection getConnection（String url,String user,String password;
2. Connection:数据库连接对象
- 功能：
1.获取执行sql对象
- Statement createStatement（）
- PrepareStatement prepareStatement（String sql）;
2.管理事务：
- 开启事务：
- 提交事务：
- 回滚事务：
3.Statement：执行sql的 对象
- 执行SQL语句
4.ResultSet：结果集对象，封装结果
- Boolean next（）：游标向下一位，判断是否有数据，是否是最后一行
- getxxx(参数)：获取数据
-
xxx代表参数类型
-
参数：
1.int: 代表列的编号，从一开始，意思是想查第几列数据，如：getString（1）
2.String: 代表列的名称，如getDouble（"balance"）public class test2 { public static void main(String[] args) throws Exception { Connection connection = null; Statement statement = null; ResultSet resultSet = null; { try { String sql = "select * from dept"; connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "123456"); statement = connection.createStatement(); //resultSet获取数据方式 resultSet = statement.executeQuery(sql); resultSet.next(); //如果不next，获取的就是各字段名 int id=resultSet.getInt(1); String name=resultSet.getString("dname"); String loc=resultSet.getString("loc"); System.out.println(id+" "+name+" "+loc); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { connection.close(); statement.close(); } } } }
-
以上Result代码集在获取数据时不知道是否是最后一行，所以应该做如下改进
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString("dname");
String loc = resultSet.getString("loc");
System.out.println(id + " " + name + " " + loc);
}
测试代码
实体类
package cn.itcast.reflect.demo;
public class dept {
private int id;
private String dname;
private String loc;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public String toString() {
return "dept{" +
"id=" + id +
", dname='" + dname + '\'' +
", lco='" + loc + '\'' +
'}';
}
}
主类
package cn.itcast.reflect.demo;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class test {
public static void main(String[] args) {
List<dept> deptList=new test().findAll();
System.out.println(deptList);
}
public List<dept> findAll(){
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
dept dept1=null;
List <dept> depts=null;
try {
// Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/db3","root","123456");
String sql="select * from dept";
statement=connection.createStatement();
resultSet=statement.executeQuery(sql);
depts=new ArrayList<>();
while (resultSet.next()){
dept1=new dept();
dept1.setId(resultSet.getInt("id"));
dept1.setDname(resultSet.getString("dname"));
dept1.setLoc(resultSet.getString("loc"));
depts.add(dept1);
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} if (connection!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return depts;
}
}
利用JDBC工具类配置
-
配置文件jdbc.properties
url=jdbc:mysql://localhost:3306/db3 user=root password=123456 driver=com.mysql.cj.jdbc.Driver -
JDBC工具类
package cn.itcast.reflect.demo; import java.io.FileReader; import java.io.IOException; import java.net.URL; import java.sql.*; import java.util.Properties; public class JDBCUtils { private static String password; private static String user; private static String url; private static String driver; static { try { //创建Properties集合类（键值对） Properties properties = new Properties(); //获取src路径下文件，Classloader类加载器 ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL resource = classLoader.getResource("jdbc.properties"); String path = resource.getPath(); //加载文件 properties.load(new FileReader(path)); //获取数据 user = properties.getProperty("user"); password = properties.getProperty("password"); url = properties.getProperty("url"); driver = properties.getProperty("driver"); //注册驱动 try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } } /* 获取连接和连接对象 * */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } /* * 释放资源 * */ public static void close(Statement statement, Connection connection) { //释放statement if (statement != null) { try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } //释放connection if (connection != null) { try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } public static void close(ResultSet resultSet, Statement statement, Connection connection) { //释放result if (resultSet != null) { try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } //释放statement if (statement != null) { try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } //释放connection if (connection != null) { try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } -
主方法
package cn.itcast.reflect.demo; import java.sql.*; import java.util.ArrayList; import java.util.List; public class testUtils { public static void main(String[] args) { List<dept> deptList = new test().findAll(); System.out.println(deptList); } public List<dept> findAll() { Connection connection = null; //连接对象 Statement statement = null; //执行对象 ResultSet resultSet = null; //结果集 dept dept1 = null; //实体类对象 List<dept> depts = new ArrayList<>(); //数据返回的集合 try { connection = JDBCUtils.getConnection(); //获取JDBC工具类连接 String sql = "select * from dept"; //sql语句 statement = connection.createStatement(); //connection获取执行sql的对象 resultSet = statement.executeQuery(sql); //执行sql的对象statement执行sql语句，返回给结果集 while (resultSet.next()) { //当结果集中还有下一条数据时 dept1 = new dept(); dept1.setId(resultSet.getInt("id")); dept1.setDname(resultSet.getString("dname")); dept1.setLoc(resultSet.getString("loc")); depts.add(dept1); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(resultSet, statement, connection); } return depts; } }
登录练习
新建数据库user内有username和password
-
配置文件
url=jdbc:mysql://localhost:3306/user user=root password=123456 driver=com.mysql.cj.jdbc.Driver -
JDBC工具类和上面的一样
-
主方法(该练习有sql注入风险)
import cn.itcast.reflect.demo.JDBCUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class user { public static void main(String[] args) { Scanner scanner=new Scanner(System.in); System.out.println("请输入用户名："); String s1=scanner.nextLine(); System.out.println("请输入密码："); String s2=scanner.nextLine(); boolean b=new user().login(s1,s2); if (b){ System.out.println("登录成功！"); }else System.out.println("登陆失败！"); } public boolean login(String username, String password) { if (username == null || password == null) { return false; } Connection connection = null; Statement statement = null; ResultSet resultSet=null; try { String sql = "select * from users where username='" + username + "'and password= '"+ password+"' "; connection = JDBCUtils.getConnection(); statement = connection.createStatement(); resultSet=statement.executeQuery(sql); return resultSet.next(); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JDBCUtils.close(resultSet,statement,connection); } return false; } }
5.PreparedStatement：执行sql的对象
用来解决sql注入问题，参数使用 ？ 占位符，如下
String sql = "select * from users where username=? and password=?";
获取执行sql的对象
connect.prepareStatement(sql)
给？赋值
prepareStatement.setString(1，username) // 1 为占位符？的位置，String为数据类型
使用PrepareStatement修改数据库
-
主方法（其他类略有修改，参考上文）
package cn.itcast.reflect;import cn.itcast.reflect.demo.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class acount {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement1=null;
PreparedStatement preparedStatement2=null;
//用户1
String sql1="Update acount set bouns = ? where id=?";
//用户2
String sql2="Update acount set bouns = ? where id=?";
try {
connection= JDBCUtils.getConnection();
preparedStatement1=connection.prepareStatement(sql1);
preparedStatement2=connection.prepareStatement(sql2);
//设置参数
preparedStatement1.setInt(1,500);
preparedStatement1.setInt(2,1);preparedStatement2.setInt(1,500); preparedStatement2.setInt(2,2); preparedStatement1.execute(); preparedStatement2.execute(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.close(preparedStatement1); JDBCUtils.close(preparedStatement2); } }}
JDBC事务回滚
package cn.itcast.reflect;
import cn.itcast.reflect.demo.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class acount {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
//用户1
String sql1 = "Update acount set bouns = ? where id=?";
//用户2
String sql2 = "Update acount set bouns = ? where id=?";
try {
connection = JDBCUtils.getConnection();
//开启事务
connection.setAutoCommit(false);
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement2 = connection.prepareStatement(sql2);
//设置参数
preparedStatement1.setInt(1, 500);
preparedStatement1.setInt(2, 1);
preparedStatement2.setInt(1, 500);
preparedStatement2.setInt(2, 2);
preparedStatement1.execute();
preparedStatement2.execute();
//提交事务
connection.commit();
} catch (Exception throwables) {
//事务回滚
throwables.printStackTrace();
try {
if (connection != null)
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
JDBCUtils.close(preparedStatement1);
JDBCUtils.close(preparedStatement2);
}
}
}
浙公网安备 33010602011771号