这篇博客介绍了JDBC的基础知识，包括如何导入JDBC库，建立数据库连接，使用Statement和PreparedStatement执行SQL，特别是PreparedStatement的预编译特性以防止SQL注入攻击。此外，还详细讲解了ResultSet的遍历以及事务处理，强调了MySQL中只有InnoDB表支持事务。最后提到了可滚动结果集的各种指针移动方法。
- 导入JDBC jar包
直接放入web项目中,一个WebRoot下WEB-INF下lib的文件夹内即可. - 建立与数据库的联系
package han_Day01_28_1_JDBC;
List item
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
/*
测试
*/
public class TestJDBCDemo1 {
public static void main(String[] args){
try {
Class.forName("com.mysql.jdbc.Driver");
// 建立与数据库的联系;
Connection connection = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8","root","admin");
System.out.println("connection successfully~" + connection);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
- statement 适用于执行SQL语句的, 比如增加, 删除
- 插入一个数据
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBCDemo2 {
public static void main(String[] args){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8","root","admin");
Statement statement = connection.createStatement();
// 使用statement 语句对数据库进行增删改查
System.out.println("statement : " + statement);
//实现数据的增删改查, 插入一个数据
String sql = "insert into students values(1," + "'cxq'" + ","+ 19 + ")";
statement.execute(sql);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
- 一个小练习 (插入数据练习)
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DemoTest_1 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8",
"root", "admin");
statement = connection.createStatement();
for (int i = 1; i <= 100; i++) {
//''单引号引起来的是一组数据
String sql = "insert students values(" + i + "," + "'students_" + i + "'," + 50 + ")";
System.out.println(sql);
statement.execute(sql);
}
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
}
}
- JDBC的增删改查
/*
*CRUD是最常见的数据库操作，即增删改查
C 增加(Create)
R 读取查询(Retrieve)
U 更新(Update)
D 删除(Delete)*
*/
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DemoTest_2 {
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
try {
// 必须引入jdbc里的Driver类, 这样才能实现操作
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8","root", "admin");
statement = connection.createStatement();
// 删除表中所有数据
String sql = "DELETE FROM students";
statement.execute(sql);
System.out.println("delete successfully~");
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 增删改查
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DemoTest_2 {
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
try {
// 必须引入jdbc里的Driver类, 这样才能实现操作
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8","root", "admin");
statement = connection.createStatement();
// 对表中所有数据进行增删改查
//增加
//String sql_add = "insert into students values (101,'students_101',50)";
//statement.execute(sql_add);
//删除
//String sql_delete = "delete from students where id = 101";
//statement.execute(sql_delete);
//修改
//String sql_change = "update students set name = 'Han' where id = 101";
//statement.execute(sql_change);
System.out.println("delete successfully~");
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 查询
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DemoTest_2 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
// 必须引入jdbc里的Driver类, 这样才能实现操作
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8",
"root", "admin");
statement = connection.createStatement();
// 对表中所有数据进行增删改查
// 增加
// String sql_add = "insert into students values
// (101,'students_101',50)";
// statement.execute(sql_add);
// 删除
// String sql_delete = "delete from students where id = 101";
// statement.execute(sql_delete);
// 修改
// String sql_change = "update students set name = 'Han' where id =
// 101";
// statement.execute(sql_change);
// 查询
String sql_select = "select * from students";
ResultSet res = statement.executeQuery(sql_select);
while (res.next()) {
int id = res.getInt("id");
String name = res.getString("name");
int age = res.getInt("age");
System.out.println(id + " : " + name + " : " + age);
}
System.out.println("delete successfully~");
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 练习
要求 : sql语句判断账号密码是否正确
i) 先建立一个类用来判断是否正确
package han_Day01_28_1_JDBC;
public class Students {
private int id;
private String name;
private int age;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public boolean equals(Object obj) {
if (obj instanceof Students) {
Students students = (Students) obj;
if (students.id == this.id && students.name.equals(this.name) && students.age == this.age) {
return true;
} else {
return false;
}
} else {
return false;
}
}
public Students(){}
public Students(int sid, String sname, int sage){
this.id = sid;
this.name = sname;
this.age = sage;
System.out.println("建立对象成功!");
System.out.println("id = " + this.id);
System.out.println("name = " + this.name);
System.out.println("age = " + this.age);
System.out.println("-------------------------------------");
System.out.println();
}
}
ii) 再试着判断是否正确
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
测试用户输入的账户密码是否正确
*/
public class TestPassword {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8",
"root", "admin");
statement = connection.createStatement();
String sql_select = "select * from students";
ResultSet res = statement.executeQuery(sql_select);
Students studentsLocal = new Students(101, "Han", 50);
while (res.next()) {
Students studentsTest = new Students(res.getInt("id"), res.getString("name"), res.getInt("age"));
if (studentsTest.equals(studentsLocal)) {
System.out.println("You are currect!");
break;
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 获得所有数据的数目
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
注意要关闭资源, ResltSet --> Statement --> Connection
*/
public class TestCountTheTotal {
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
ResultSet res = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8",
"root", "admin");
statement = connection.createStatement();
String sql_count = "select count(*) from students";
res = statement.executeQuery(sql_count);
int i = 0;
while (res.next()){
i = res.getInt(1);
}
System.out.println("The total number is " + i);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(res != null){
try {
res.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();
}
}
}
}
}
- 预编译prepareStatement
与statement一样, preparedStatement也是用来执行sql语句的.
但是与常见Statement不同的是, 需要根据sql语句创建PreparedStatement
除此之外还能够通过设置参数, 制定相应的值, 而不是statement那样使用字符串的拼接
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestPreparedStatement {
public static void main(String[] args){
Connection connection = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8",
"root", "admin");
String sql = "insert students values(?,?,?)";
ps = connection.prepareStatement(sql);
ps.setInt(1, 102);
ps.setString(2, "cxq");
ps.setInt(3, 50);
ps.execute();
System.out.println("done!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 使用execute 增删改查
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestExecuteUpdate {
public static void main(String[] args){
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8","root","admin");
String sql_add = "insert students values(?,?,?)";
String sql_update = "update students set age = 100 where id = 103";
String sql_delete = "delete from students where id = 103";
// PreparedStatement ps_add = connection.prepareStatement(sql_add);
// ps_add.setInt(1, 103);
// ps_add.setString(2, "HAN");
// ps_add.setInt(3, 103);
// ps_add.execute();
// ps_add.close();
//
// PreparedStatement ps_update = connection.prepareStatement(sql_update);
// ps_update.execute();
PreparedStatement ps_delete = connection.prepareStatement(sql_delete);
ps_delete.execute();
System.out.println("done");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 获取自增长的值
i)
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class CloseConnections {
private Connection connection;
private PreparedStatement preparedStatement;
private Statement statement;
public Connection getConnection() {
return connection;
}
public void setConnection(Connection connection) {
this.connection = connection;
}
public PreparedStatement getPreparedStatement() {
return preparedStatement;
}
public void setPreparedStatement(PreparedStatement preparedStatement) {
this.preparedStatement = preparedStatement;
}
public Statement getStatement() {
return statement;
}
public void setStatement(Statement statement) {
this.statement = statement;
}
public CloseConnections() {
}
public CloseConnections(Connection c, PreparedStatement ps, Statement s) {
this.connection = c;
this.preparedStatement = ps;
this.statement = s;
}
public void Close() {
if (connection != null) {
try {
connection.close();
System.out.println("Success in closing connection ! ");
} catch (SQLException e) {
System.out.println("Fail in closing connection ! ");
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
System.out.println("Success in closing preparedStatement ! ");
} catch (SQLException e) {
System.out.println("Fail in closing preparedStatement ! ");
}
}
if (statement != null) {
try {
statement.close();
System.out.println("Success in closing statement ! ");
} catch (SQLException e) {
System.out.println("Fail in closing preparedStatement ! ");
}
}
}
}
ii)
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestAutoIncrease {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?charaterEncoding=UTF-8",
"root", "admin");
String sql = "insert students values(null,?,?)";
ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "ccc");
ps.setInt(2, 50);
ps.execute();
ResultSet res = ps.getGeneratedKeys();
int number = 0;
while (res.next()) {
number = res.getInt(1);
System.out.println(number);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// closeConnections 已经封装完毕
new CloseConnections(connection, ps, null).Close();
}
}
}
- 获取数据库的元数据
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/*
获取数据库的元数据
例如 : 数据库服务器版本, 驱动版本, 都有哪些数据库等信息
*/
public class TestGetMetaData {
public static void main(String[] args){
Connection connection = null;
DatabaseMetaData dmd = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?charaterEncoding=UTF-8","root","admin");
dmd = connection.getMetaData();
//通过元数据, 获得数据库服务器版本, 驱动版本, 都有哪些数据库等信息
// 获取数据库产品名称
System.out.println("数据库产品名称 : " + dmd.getDatabaseProductName());
//获取数据库版本号
System.out.println("数据库版本号 : "+ dmd.getDatabaseProductVersion());
//获取数据库服务器用作类别与表名之间的分隔符
System.out.println("数据库与表名之间的分隔符 : " + dmd.getCatalogSeparator());
//获取驱动版本
System.out.println("驱动版本 : " + dmd.getDriverVersion());
//获取数据库名称
ResultSet res = dmd.getCatalogs();
while(res.next()){
System.out.println("数据库名称 : " + res.getString(1));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 练习-自增长id
当插入一条数据之后, 通过获取自增长id,得到这条数据的id, 并删除之前挑一条数据, 如果id是55, 那就要删除54,54没有就删除id为53的, 以此类推, 直到删除掉上一条数据为止.
package han_Day01_28_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
public class TestDeleteOnePractise {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement ps = null;
PreparedStatement ps_delete = null;
PreparedStatement ps_select = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?charaterEncoding=UTF-8",
"root", "admin");
String sql = "insert students values(null,?,?)";
ps = connection.prepareStatement(sql);
ps.setString(1, "han");
ps.setInt(2, 100);
ps.execute();
ResultSet res = ps.getGeneratedKeys();
int key = 0;
while (res.next()) {
key = res.getInt(1);
}
System.out.println("key : " + key);
// 从数据库里全部拿出所有的students
String sql_select = "select * from students";
ps_select = connection.prepareStatement(sql_select);
ResultSet res_select = ps_select.executeQuery();
HashSet<Students> hashSet = new HashSet<Students>();
while (res_select.next()) {
int id = res_select.getInt("id");
String name = res_select.getString("name");
int age = res_select.getInt("age");
System.out.println(id);
System.out.println(name);
System.out.println(age);
Students students = new Students(id, name, age);
hashSet.add(students);
System.out.println("添加ID : " + id + " 成功!");
}
System.out.println("装载进HashSet, done!");
// 去寻找相对应的数据并删除
String sql_delete = "delete from students where id = ?";
ps_delete = connection.prepareStatement(sql_delete);
int i = key - 1;
int j = 0;
System.out.println("i = " + i);
System.out.println("---------------------------");
for (; i >= 0; i--) {
for (Students s : hashSet) {
if (i == 0) {
System.out.println("找不到任何数据");
j = 0;
break;
}
if (s.getId() == i) {
ps_delete.setInt(1, i);
ps_delete.execute();
System.out.println("s.getId() - A : " + s.getId());
System.out.println("删除ID : " + i + " 成功!");
j = i;
break;
}
System.out.println("s.getId() - B : " + s.getId());
System.out.println("ID : " + i);
System.out.println("---------------------------");
}
if(i == j){
break;
}
}
System.out.println("所有都完成了!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
CloseConnections cos = new CloseConnections(connection, ps, null);
cos.Close();
try {
ps_delete.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps_delete.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 事务学习
package han_Day01_30_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*
1. 事务学习
事务的作用 : 保证数据的一致性, 但是可能会导致线程堵塞.
2. 在事务操作中, 要么都成功, 要么都失败:
如果想使同一事务同时成功 则需要两个操作
1) 关闭自动提交 : connection.setAutoCommit(false);
2) 手动提交 : connection.commit();
如果事务中有一句语句出错, 那就不会对数据库形成影响, 数据不变.
*/
public class TestAffair {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8",
"root", "admin");
statement = connection.createStatement();
String sql_plus = "update students set age = age + 1 where id = 122";
String sql_minus = "update students set age = age - 2 where id = 122";
connection.setAutoCommit(false);
statement.execute(sql_plus);
statement.execute(sql_minus);
connection.commit();
System.out.println("done!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
System.out.println("sql 语句出错 || sql语句执行失败");
} finally {
CloseConnections coc = new CloseConnections(connection, null, statement);
coc.Close();
}
}
}
- MYSQL 表的类型必须是INNODB才支持事务
在Mysql中,只有当表的类型是INNODB的时候, 才支持事务, 所以需要把表的类型设置为INNODB, 否则无法观察到事务.
在mysql数据库中, 可以观察并修改表的类型
- 修改表的类型为INNODB : alter table (表名) ENGINE = innodb;
- 查看表的类型 : show table status from (表名)
- 事务练习
设计一段代码, 删除表中前十个数据, 但是在删除前在控制台上弹出一个提示, 是否删除(Y/N)
package han_Day01_30_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class TestAffairExercise {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement ps = null;
PreparedStatement ps_delete = null;
Scanner scanner = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8",
"root", "admin");
// 寻找数据库中前十个数据, 并把id值赋给一个数组
String sql_select = "select * from students";
ps = connection.prepareStatement(sql_select);
ResultSet res_select = ps.executeQuery();
int[] id = new int[10];
int i = 0;
while (res_select.next()) {
if (i > 9) {
break;
}
id[i] = res_select.getInt("id");
System.out.println(id[i]);
i++;
}
// 从数组中调取id, 并循环取出, 询问是否删除,然后执行删除命令.
String sql_delete = "delete from students where id = ?";
ps_delete = connection.prepareStatement(sql_delete);
// 插入一个控制台输入语句, 接受输入 && 在执行事务的时候, 要统统对了才会执行, 事务一起执行
System.out.println("是否要删除该项目Y/N");
scanner = new Scanner(System.in);
String order = scanner.next();
System.out.println("order.equals('Y') : " + order.equals("Y"));
if (order.equals("Y")) {
connection.setAutoCommit(false);
for (int j = 0; j < 10; j++) {
System.out.format("ID = %d is going to be deleted!%n" , id[j]);
ps_delete.setInt(1, id[j]);
ps_delete.execute();
System.out.format("ID = %d has been deleted!%n" , id[j]);
}
connection.commit();
System.out.println("Y : 数据已删除.");
} else {
System.out.println("N : 数据未删除.");
}
System.out.println("the delete is been done!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
CloseConnections cos = new CloseConnections(connection, ps, ps_delete);
cos.Close();
if(scanner != null){
scanner.close();
}
}
}
}
- ORM 学习
ORM = Object Relationship Data Mapping
对象和关系数据库的映射
简单说, 一个对象, 对应数据库里的一条记录
- 封装数据库里对象的操作方法
i) 对象的插入方法
ii) 对象的删除方法
iii) 对象的更新
package han_Day01_31_1_JDBC;
import java.util.List;
/*
接口是被对象(StudentsDAO)实现的.
比如这里是由Students实现的
*/
public interface DAO {
public void add(Students students);
public void update(Students students);
public void delete(Students students);
public Students get(int id);
public List<Students> List();
}
package han_Day01_31_1_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/*
* 实现DAO接口,并用StudentsDAO实现,
*
*
*/
public class StudentsDAO implements DAO {
// 设计一个静态变量, 初始之后就给他赋值
public static String tableName;
static {
tableName = "students";
System.out.println("静态块执行kk");
}
// 1. 把驱动的初始化放在构造体内, 在类创建的过程中,最先执行, 且执行一次.
public StudentsDAO() {
System.out.println("构造方法执行");
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("失败 : 初始化驱动");
}
}
// 2. 提供一个公共连接Connection, SQLException异常抛出.
public Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/handatabase?characterEncoding=UTF-8", "root",
"admin");
}
// 3. 获得所有表中对象的数量 --> 表名不能替换, 如果用?, 是表示传参数,很重要!
public int getTotal() {
int total = 0;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
String sql_total = "select * from " + tableName;
preparedStatement = connection.prepareStatement(sql_total);
ResultSet res = preparedStatement.executeQuery();
while (res.next()) {
total++;
}
} catch (SQLException e) {
System.out.println("get total fail : getting the connnection!");
} finally {
CloseConnections closeConnections = new CloseConnections(connection, null, preparedStatement);
closeConnections.Close();
}
return total;
}
// 4. 实现DAO接口的方法, 在数据库里增加Students对象
@Override
public void add(Students students) {
String sql_add = "insert into " + tableName + " values(null,?,?)";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql_add);
preparedStatement.setString(1, students.getName());
preparedStatement.setInt(2, students.getAge());
preparedStatement.execute();
// 获取自增id的参数
ResultSet res = preparedStatement.getGeneratedKeys();
while (res.next()) {
System.out.println(res.getInt(1));
}
} catch (SQLException e) {
System.out.println("add fail : getting the connnection!");
} finally {
CloseConnections closeConnections = new CloseConnections(connection, preparedStatement, null);
closeConnections.Close();
}
}
// 4. 实现DAO接口的方法, 在数据库里更新Students对象
public void update(Students students) {
String sql_update = "update " + tableName + " set name = ? , age = ? where id = 11";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql_update);
preparedStatement.setString(1, students.getName());
preparedStatement.setInt(2, students.getAge());
preparedStatement.execute();
} catch (SQLException e) {
System.out.println("update fail : getting the connnection!");
} finally {
CloseConnections closeConnections = new CloseConnections(connection, preparedStatement, null);
closeConnections.Close();
}
}
// 4. 实现DAO接口的方法, 在数据库里删除Students对象
public void delete(Students students) {
String sql_delete = "delete from " + tableName + " where id = ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql_delete);
preparedStatement.setInt(1, students.getId());
preparedStatement.execute();
} catch (SQLException e) {
System.out.println("fail : getting the connnection!");
} finally {
CloseConnections closeConnections = new CloseConnections(connection, preparedStatement, null);
closeConnections.Close();
}
}
@Override
public Students get(int id) {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql_getId = "select * from " + tableName;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql_getId);
ResultSet res = preparedStatement.executeQuery();
ArrayList<Students> studentsArraylist = new ArrayList<Students>();
while (res.next()) {
Students students = new Students(res.getInt("id"), res.getString("name"), res.getInt("age"));
studentsArraylist.add(students);
System.out.println("students id : " + students.getId());
System.out.println("students name : " + students.getName());
System.out.println("students id : " + students.getAge());
System.out.println("_____________________________________");
}
for (int i = 0; i < studentsArraylist.size(); i++) {
int studentsId = studentsArraylist.get(i).getId();
String studentsName = studentsArraylist.get(i).getName();
int studentsAge = studentsArraylist.get(i).getAge();
if (studentsId == id) {
System.out.println("_________Find this student!_______");
System.out.println("students id : " + studentsId);
System.out.println("students name : " + studentsName);
System.out.println("students id : " + studentsAge);
System.out.println("__________________________________");
return new Students();
}
}
System.out.println("_________cannot find this student!_______");
return null;
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("_________cannot find this student!_______");
return null;
}
// 把数据库里所有对象拿出来
public ArrayList<Students> List() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql_getId = "select * from " + tableName;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql_getId);
ResultSet res = preparedStatement.executeQuery();
ArrayList<Students> studentsArraylist = new ArrayList<Students>();
while (res.next()) {
Students students = new Students(res.getInt("id"), res.getString("name"), res.getInt("age"));
studentsArraylist.add(students);
System.out.println("students id : " + students.getId());
System.out.println("students name : " + students.getName());
System.out.println("students id : " + students.getAge());
System.out.println("_____________________________________");
}
return studentsArraylist;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
package han_Day01_31_1_JDBC;
public class TestTry {
public static void main(String[] args) {
// 获得表中说有数据的总数
// int total = new StudentsDAO().getTotal();
// System.out.println("total : " + total);
// 插入数据库新Students对象
// Students students = new Students(0, "hxt", 100);
// new StudentsDAO().add(students);
// 更新数据库对象
// Students students_update = new Students(1, "hhh", 100);
// new StudentsDAO().update(students_update);
// 删除数据库里某一个对象
//Students students_delete = new Students(103, null, 0);
//new StudentsDAO().delete(students_delete);
// 寻找数据库里指定ID的数据
int id = 100;
new StudentsDAO().get(id);
}
}
- excel导入mysql
https://blog.csdn.net/weixin_38437243/article/details/78974346
**. jdbc 使用方法
- Statement只适合执行静态SQL语句，因为执行动态SQL语句有两个缺点：
- 1.由于含有动态信息，那么就需要先拼接SQL，这就可能出现SQL注入攻击的问题。
- 2.大部分情况下，拼接SQL时，语义已经定好，拼接的内容无非就是一些数据，
- 那么当大量执行这样含有动态值的SQL语句时，数据库每当接收到Statement发送的SQL语句时，
- 只要语句中的内容有区别，就会当做一条全新的SQL语句去执行，
- 数据库执行SQL时会首先解析SQL语句并生成一个执行计划（开销大），那么批量执行这样的内容有些微变化的
- SQL时，会为每一个SQL生成一个执行计划，对数据库是负担。
执行计划：一段可执行的代码
-
java.sql.PreparedStatement
-
该接口是Statement的子接口，设计目的是为了执行动态SQL语句。
-
这样的SQL称为预编译SQL，这种SQL语句会将动态信息以“？”代替，
-
先进行占位，然后将该SQL发送给数据库生成的执行计划。
-
然后当需要执行该SQL时，只需要将？需要的时机数据再次传递给数据库即可。
-
1.由于先将SQL语句发送给数据库，并生成了执行计划（语义已经确定），
-
就不存在拼接SQL导致改变语义（SQL注入攻击）的问题了。
-
2.由于执行计划已经生成，当大批量执行SQL时，每次只需要将？表示的实际值传入，
-
那么数据库会重用执行计划，这就减少了服务器的压力。
//使用PreparedStatement /* * 预编译SQL语句，将动态部分以？代替 * 需要注意，？只能代替值，不能代替会改变SQL语句语义的部分 */
EG：执行DQL
String sql = "SELECT id,username,password,email,nickname "
+ "FROM userinfo_snow "
+ "WHERE username=? AND password=? ";
/*
* 创建PreparedStatement时就需要将预编译SQL语句传入，
* 实际上在创建PS时就会将该SQL发送给数据库以生成执行计划。
*/
PreparedStatement ps = conn.prepareStatement(sql);
/*
* 在执行SQL的工作前，还需要将？部分的时机数据传递给数据库
*/
ps.setString(1, "MARRY");
ps.setString(2, "123");
ResultSet rs = ps.executeQuery();
if(rs.next()){
System.out.println("登录成功！");
}else{
System.out.println("登录失败！");
}
EG：执行DML
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@176.225.131.254:1521:orcl", "tarena", "tarena");
//使用PreparedStatement
String sql = " INSERT INTO userinfo_snow "
+ " (id,username,password,email,nickname,account) "
+ " VALUES "
+ " (seq_userinfo_snow_id.NEXTVAL,?,?,?,?,?) ";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "Halin");
ps.setString(2, "123456");
ps.setString(3, "HL@qq.com");
ps.setString(4, "HL");
ps.setDouble(5, 6000);
int d = ps.executeUpdate();
if(d>0){
System.out.println("插入成功！");
}
EG:
/**
- 要求用户传入一个id，然后删除对应的用户
- 使用PreparedStatement完成
- @author Tarena-java
*/
public class JDBCDemo03 {
public static void main(String[] args) {
Scanner scan = new Scanner(System.in);
System.out.println(“请输入一个id：”);
try {
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@176.225.131.254:1521:orcl”, “tarena”, “tarena”);
int id = scan.nextInt();
String sql = "DELETE userinfo_snow "
+ " WHERE id = ? ";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
int i = ps.executeUpdate();
if(i>0){
System.out.println(“删除”+id+“对应的信息成功！”);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
EG：
/*
* 获取结果集元数据
*/
ResultSetMetaData rsmd = rs.getMetaData();
/*
* 查看结果集字段数量
*/
int count = rsmd.getColumnCount();
for(int i=1;i<=count;i++){
/*
* 查看指定字段的字段名
*/
String colName = rsmd.getColumnName(i);
System.out.println(colName);
}
建立一个管理数据库连接的类
1.定义静态属性
private static String className;
private static String url;
private static String userName;
private static String passWord;
2.在静态块里面初始化静态方法（注意，此时不能在构造方法里面初始化，因为需要在加载类的时候就能初始化属性）
static{
//1.加载配置文件
/*
* java.util.Properties
* 用来读取.properties文件，并解析其中的每一行内容，然后以key-value的形式保存在当前实例中
className=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@176.225.131.254:1521:orcl
userName=tarena
passWord=tarena
*/
Properties prop = new Properties();
try {
prop.load(new FileInputStream("config.properties"));
className = prop.getProperty("className");
url = prop.getProperty("url");
userName = prop.getProperty("userName");
passWord = prop.getProperty("passWord");
System.out.println(className);
System.out.println(url);
System.out.println(userName);
System.out.println(passWord);
} catch (Exception e) {
e.printStackTrace();
}
连接：尽可能重用，控制数量——> 连接池
EG：建立连接池
private static BasicDataSource ds;
//初始化连接池
ds = new BasicDataSource();
//将JDBC建立连接所需要的信息设置到连接池中（这时可以将前面定义的静态属性删掉）
//Class.forName(...)
ds.setDriverClassName(className);
//DriverManager.getConnection(...)
ds.setUrl(url);
ds.setUsername(userName);
ds.setPassword(passWord);
/**
* 获取数据库连接
* @return
*/
public static Connection getConnection() throws Exception{
/*
* 连接池提供的方法：
* Connection getConnection()
* 该方法可以返回一个连接池中可用连接。
* 这是一个阻塞方法，当连接池中有空闲连接可以使用时，会立即返回，
* 若当前连接池没有可用连接，会进入阻塞，阻塞时间由创建连接池时通过
* setMaxWait设置的时间为准，在等待期间若有空闲连接则立即返回。
* 当超过最大等待时间仍没有可用连接时，该方法会抛出超时异常。
*/
return ds.getConnection();
}
/**
* 关闭给定的连接
* @param conn
*/
public static void closeConnection( Connection conn){
try {
/*
* 若该连接是通过连接池获取的，那么调用这个连接的close方法并不是与数据库断开连接了，
* 而仅仅是将该连接还给连接池
*/
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
EG：
* 转账业务：
* 首先必须是登录用户
* 要求用户输入转入账号的用户名，然后输入转出的金额，
* 该金额必须小于等于当前用户的余额，然后执行SQL语句，
* 将当前用户的余额与转入账户余额进行相应的修改。最终通知当前用户操作是否成功。
private void transforAccount(){
if(userInfo == null){
System.out.println("请先登录！");
return;
}
String accountName = null;
double money; //转账金额
double money_other;
Scanner scan = new Scanner(System.in);
System.out.println("请输入转账对象的用户名：");
try {
conn = DBUtil.getConnection();
// 判断转账的人是否存在
while(true){
accountName = scan.next();
String sql1 = "SELECT account "
+ " FROM userinfo_snow "
+ " WHERE username = ? ";
ps = conn.prepareStatement(sql1);
ps.setString(1, accountName);
ResultSet rs = ps.executeQuery();
if(rs.next()){
money_other = rs.getDouble("account");
break;
}
System.out.println("您要转账的对象不存在！请重新输入！");
continue;
}
System.out.println("请输入转账金额");
//判断转账金额是否合理
while(true){
money = scan.nextDouble();
if(money>userInfo.getAccount()){
System.out.println("转账金额大于您的余额，请重新输入转账金额！");
continue;
}
break;
}
String sql2 = "UPDATE userinfo_snow "
+ " SET account =? "
+ " WHERE username =? ";
ps = conn.prepareStatement(sql2);
double money1 = userInfo.getAccount()-money;
money_other = money_other + money;
// 更改用户余额
ps.setDouble(1, money1);
ps.setString(2, userInfo.getUsername());
int i1 = ps.executeUpdate();
if(i1>0){
System.out.println("用户余额修改成功！");
}
// 更改转账对象金额
ps.setDouble(1, money_other);
ps.setString(2, accountName);
//执行SQL语句
int i2 = ps.executeUpdate();
if(i2>0){
System.out.println("转账对象余额修改成功！");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- JDBC核心API
1.1. Statement
1.1.1. Statement执行查询
通过Connection对象创建Statement的方式：
Connection.createStatement();
执行INSERT, UPDATE和DELETE等DML操作：
Statement.executeUpdate();
执行SELECT：
Statement.executeQuery();
通过Statement对象返回SQL语句执行后的结果集:
String sql = “select empno, ename, sal, hiredate from emp”;
Statement stmt = con.createStatement();
ResultSetrs = stmt.executeQuery(sql);
//对rs的处理
//代码略
stmt.close();
1.1.2. Statement执行插入
Statement.executeUpdate(sql)方法将返回SQL语句执行后影响的记录数:
String sql = “insert into emp(empno, ename, job, sal) values(1001, ‘张三丰’,‘Manager’9500)”;
int flag = -1；
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
flag = stmt.executeUpdate(sql);
//处理结果
}catch(SQLException e){
//处理异常
}
1.1.3. Statement执行更改
和INSERT操作完全相同，只是SQL语句不同：
String sql = “update emp set sal = 9900 where empno = 1001”;
int flag = -1；
try {
con = ConnectionSource.getConnection();
stmt = con.createStatement();
flag = stmt.executeUpdate(sql);
//处理结果
}catch(SQLException e){
//处理异常
}
1.2. PreparedStatement
1.2.1. PreparedStatement原理
Statement主要用于执行静态SQL语句，即内容固定不变的SQL语句。Statement每执行一次都要对传入的SQL语句编译一次，效率较差。
某些情况下，SQL语句只是其中的参数有所不同，其余子句完全相同，适用于PreparedStatement。
PreparedStatement的另外一个好处就是预防sql注入攻击。
PreparedStatement是接口，继承自Statement接口。
使用PreparedStatement时，SQL语句已提前编译，三种常用方法 execute、executeQuery和executeUpdate已被更改，以使之不再需要参数。
图-1 PreparedStatement原理
PreparedStatement实例包含已事先编译的 SQL 语句，SQL 语句可有一个或多个 IN 参数，IN参数的值在 SQL 语句创建时未被指定。该语句为每个 IN 参数保留一个问号（“？”）作为占位符。
每个问号的值必须在该语句执行之前，通过适当的setInt或者setString等方法提供。
由于PreparedStatement对象已预编译过，所以其执行速度要快于 Statement 对象。因此，多次执行的 SQL 语句经常创建为PreparedStatement对象，以提高效率。
通常批量处理时使用PreparedStatement。
//SQL语句已发送给数据库，并编译好为执行作好准备
PreparedStatementpstmt = con.prepareStatement(
“UPDATE emp SET job= ? WHERE empno= ?”);
//对占位符进行初始化
pstmt.setLong(1, “Manager”);
pstmt.setInt(2,1001);
//执行SQL语句
pstmt.executeUpdate();
1.2.2. 通过PreparedStatement提升性能
图-2 数据库执行SQL语句过程
在数据库执行SQL语句过程中，制定执行计划开销巨大。
数据库本身具备SQL缓存功能，可以对statement的执行计划进行缓存，以免重复分析。其缓存原理是使用statement本身作为key并将执行计划存入与statement对应的缓存中，对曾经执行过的statements，再运行时执行计划将重用。
举例：
SELECT a, b FROM t WHERE c = 1；
再次向数据库发送相同的statement时，数据库会对先前使用过的执行计划进行重用，降低开销。
但是，如下两条语句被视作不同的SQL语句,执行计划不可重用：
SELECT a, b FROM t WHERE c = 1；
SELECT a, b FROM t WHERE c = 2；
这就是为什么要使用PreparedStatement：
//执行计划可重用
String sql =“select a,b from t where c = ?”;
PreparedStatementps = conn.prepareStatement(sql);
for (inti = 0; i< 1000; i++) {
ps.setInt(1, i);
ResultSetrs = ps.executeQuery();
//处理rs，省略
rs.close();
}
ps.close();
1.2.3. SQL Injection简介
场景：如下SQL语句被发送到数据库中：
String sql = “select * from t where username = '” + name + “’ and password = '” + passwd + “’”;
输入用户名和密码参数后，数据库接受到的完整sql语句将是这种形式：
select * from t where username = ‘scott’ and password = ‘tiger’;
如果用户输入的passwd参数是：’ or ‘1’='1, 则数据库收到的SQL语句将是：
select * from t where username = ‘scott’ and password = ‘’ or ‘1’='1’;
此SQL语句的where条件将永远为true。即用户不需要输入正确的帐号密码，也能登录。这种现象称作SQL注入（SQL Injection）。
1.2.4. 通过PreparedStatement防止SQL Injection
对JDBC而言，SQL注入攻击只对Statement有效，对PreparedStatement无效，因为PreparedStatement不允许在插入参数时改变SQL语句的逻辑结构。
使用预编译的语句对象时，用户传入的任何数据不会和原SQL语句发生匹配关系，无需对输入的数据做过滤。
如果用户将’ or ‘1’='1传入赋值给占位符，下述SQL语句将无法执行：
select * from t where username = ? and password = ?;
1.3. ResultSet
1.3.1. 结果集遍历
结果集常用的遍历方式（使用rs.getXXX方法）：
String sql = “select empno, ename, sal, hiredate from emp”;
rs = stmt.executeQuery(sql);
while (rs.next()) {
intempno = rs.getInt(“empno”);
String ename = rs.getString(“ename”);
doublesal = rs.getDouble(“sal”);
Date hiredate = rs.getDate(“hiredate”);
}
rs.close();
1.3.2. ResultSetMetaData
ResultSetMetaData: 数据结果集的元数据，和查询出来的结果集相关,从结果集(ResultSet)中获取。
下列代码获得ResultSetMetaData对象后，从中获取数据表的所有列名：
ResultSetMetaDatarsm = rs.getMetaData();
intcolumnCount = rsm.getColumnCount();
String columnName = null;
for (int i = 1; i<=columnCount; i++) {
columnName = rsm.getColumnName(i);
}
1.3.3. 可滚动结果集
（大部分情况下JDBC结果集在服务端，每次对结果集的操作都要通过网络，会降低性能，所以用的情况很少。）
常用的ResultSet，返回后，其初始指针在第一行之前（Before First），并且只能使用next()方法将指针向后移动，不能反向，一次移动一行，不能跳行。
可滚动的结果集：指针可以在结果集中任意移动。使用在需要指针移动的场合，比如分页。
获得可滚动的ResultSet，Statement或者PreparedStatement的创建有所不同：
Statement stmt = conn.createStatement(type, concurrency);
PreparedStatementstmt = conn.prepareStatement(sql, type, concurrency);
其中type取值：
TYPE_FORWARD_ONLY：只能向前移动，默认参数
TYPE_SCROLL_INSENSITIVE：可滚动，不感知数据变化
TYPE_SCROLL_SENSITIVE：可滚动，感知数据变化
concurrency取值：
CONCUR_READ_ONLY：只读
CONCUR_UPDATABLE：可更新
获得可滚动结果集后，常用方法如下：
first：指针移动到第一条
last：指针移动到最后一条
beforeFirst：指针移动到第一条之前
afterLast：指针移动到最后一条之后
isFirst：判断指针是否指向第一条
isLast：判断指针是否指向最后一条
isBeforeFirst：判断指针是否在第一条之前
isAfterLast：判断指针是否在最后一条之后
relative：移动到当前指针的相对位置
next：移动到下一条
previous：移动到前一条
absolute：移动到绝对位置
作者：HXX_M
来源：CSDN
原文：https://blog.csdn.net/HXX_M/article/details/52676111
版权声明：本文为博主原创文章，转载请附上博文链接！
1520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
