前言
复习记录一下java jdbc基础,代码已上传,下载地址
https://download.csdn.net/download/Program_boy8899/85012191
一、建立Java项目
提示:注意mysql驱动jar包和mysql数据库版本匹配,本文用的数据库为mysql1.8, 驱动jar包为8.0,项目目录如下:
二、代码Demo
1.建立数据库表(user表)
sql语句如下:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NULL DEFAULT NULL COMMENT '用户ID',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户昵称'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
2.建立user实体类
com.cxk.model包下的 User类
package com.cxk.model;
public class User {
private String Id;
private String Name;
public User() {
super();
}
public User(String id, String name) {
Id = id;
Name = name;
}
public String getId() {
return Id;
}
public void setId(String id) {
Id = id;
}
public String getName() {
return Name;
}
public void setName(String name) {
Name = name;
}
@Override
public String toString() {
return "User{" +
"Id='" + Id + '\'' +
", Name='" + Name + '\'' +
'}';
}
}
3.编写数据库连接工具类
com.cxk.util包下的 Conn类
package com.cxk.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Conn {
public static Connection getConn() {
String driver = "com.mysql.cj.jdbc.Driver";
// 数据库地址
String url = "jdbc:mysql://localhost:3306/test111?serverTimezone=GMT"; //serverTimezone 设置时区
String username = "root"; //数据库用户名
String password = "123456"; //数据库密码
Connection conn = null; // 首先创建一个空连接
try {
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password); //获取连接
} catch (ClassNotFoundException e) {
e.printStackTrace(); // jar包未找到
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
4.查询Demo(select)
4.1 查询方法
com.cxk.demo 包下的 DemoSelect类
package com.cxk.demo;
import com.cxk.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import static com.cxk.util.Conn.getConn;
public class DemoSelect {
// 查询全部记录
public List<User> select_all() {
Connection conn = getConn(); // 创建连接
String sql = "select * from user"; //查询的sql语句
PreparedStatement pstmt; // 创建一个PreparedStatement用来构建sql
List<User> userList = new ArrayList<>(); //创建一个User集合,用来储存查询结果
try {
pstmt = conn.prepareStatement(sql); // 使用PreparedStatement对象来构建SQL语句
ResultSet rs = pstmt.executeQuery(); // 使用 preStmt 执行 SQL 语句
while (rs.next()) { // 循环赋值
User user = new User(rs.getString("id"), rs.getString("name"));
userList.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return userList;
}
// 查询单条记录
public User select_one(String id){
Connection conn = getConn(); // 创建连接
String sql = "select * from user where id='" + id + "'"; //拼接where查询条件
PreparedStatement pstmt; // 创建一个PreparedStatement用来构建sql
User user = new User(); //创建一个User对象
try {
pstmt = conn.prepareStatement(sql); // 使用PreparedStatement对象来构建SQL语句
ResultSet rs = pstmt.executeQuery(); // 使用 preStmt 执行 SQL 语句
while (rs.next()) {
user.setId(rs.getString("id"));
user.setName(rs.getString("name"));
}
pstmt.close(); // 清空预编译的sql
conn.close(); // 关闭数据库资源
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
}
4.2 运行测试查询Demo
com.cxk.test包下的 Test_Select类
package com.cxk.test;
import com.cxk.demo.DemoSelect;
import com.cxk.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import static com.cxk.util.Conn.getConn;
public class Test_Select {
public static void main(String[] args) {
DemoSelect demoSelect = new DemoSelect();
// 查询全部记录
List<User> userList = demoSelect.select_all(); //查询全部记录
System.out.println("==================================");
System.out.print("Id" + "\t");
System.out.println("名称");
for (User user : userList) { // 遍历list
System.out.print(user.getId()+ "\t");
System.out.println(user.getName());
}
System.out.println("==================================");
// 查询单条记录
Scanner scanner=new Scanner(System.in); //创建一个Scanner对象从控制台获取数据
System.out.println("请输入id: "); //提示信息
String id = scanner.next(); // 获取控制台输入的ID
User user = demoSelect.select_one(id); //查询全部记录
String user_string = user.toString(); //调取实体类toString方法
System.out.println(user_string); // 输出信息
}
}
5.增加Demo(Insert)
5.1 增加的方法
com.cxk.demo包下的 DemoInsert类
package com.cxk.demo;
import com.cxk.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import static com.cxk.util.Conn.getConn;
public class DemoInsert {
// 单条数据插入
public int insert_one(User user) {
Connection conn = getConn(); // 创建数据库连接
int i = 0; // sql执行受影响行数
String sql = "insert into user (Id,Name) values(?,?)"; // sql语句
PreparedStatement pstmt; // 创建一个PreparedStatement用来构建sql
try {
pstmt = conn.prepareStatement(sql); // 构建sql
pstmt.setString(1, user.getId()); // 在上面sql中问号位置设置值
pstmt.setString(2, user.getName());
i = pstmt.executeUpdate(); // 执行编译好的sql
pstmt.close(); // 清空预编译的sql
conn.close(); // 关闭数据库资源
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("成功插入"+ i + "条数据");
return i;
}
// 多条数据插入
public int insert_many(List<User> userList) {
Connection conn = getConn();
int[] i;
int r = 0;
String sql = "insert into user (Id,Name) values(?,?)";
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement(sql);
for(User user: userList){ // 遍历userList,循环插入值
pstmt.setString(1,user.getId());
pstmt.setString(2,user.getName());
pstmt.addBatch(); // 拼接sql
}
i = pstmt.executeBatch(); //批量执行,返回受影响数组
r = i.length; // 转换为行数
pstmt.close(); // 清空预编译的sql
conn.close(); // 关闭数据库资源
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("成功插入"+ r + "条数据");
return r;
}
}
5.2 测试增加
com.cxk.test包下的 Test_Insert类
package com.cxk.test;
import com.cxk.demo.DemoInsert;
import com.cxk.model.User;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class Test_Insert {
public static void main(String[] args) {
DemoInsert demoInsert = new DemoInsert();
Scanner scanner = new Scanner(System.in);
// 插入单条数据
System.out.println("请输入ID");
String id = scanner.next();
System.out.println("请输入name");
String name = scanner.next();
User user = new User(id,name);
demoInsert.insert_one(user);
// 插入多条数据
String s = "Y";
List<User> userList = new ArrayList<>();
while (s.equalsIgnoreCase("y")){
System.out.println("请输入ID");
String id2 = scanner.next();
System.out.println("请输入name");
String name2 = scanner.next();
User user2 = new User(id2, name2);
userList.add(user2);
System.out.println("是否继续输入? (请输入 Y or N)");
s = scanner.next();
}
demoInsert.insert_many(userList);
}
}
6.更新Demo(Update)
6.1 更新的方法
com.cxk.demo包下的 DemoUpdate类
package com.cxk.demo;
import com.cxk.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import static com.cxk.util.Conn.getConn;
public class DemoUpdate {
public int update_one(User user, String id) {
Connection conn = getConn(); // 获取数据库连接
int i = 0; // 返回受影响行数
String sql = "update user set id=?, name = ? where id = '" + id + "'"; //sql语句
PreparedStatement pstmt; // 创建一个PreparedStatement用来构建sql
try {
pstmt = conn.prepareStatement(sql); // 构建sql,预编译sql
pstmt.setString(1,user.getId()); // 传参
pstmt.setString(2,user.getName());
i = pstmt.executeUpdate(); // 执行sql
pstmt.close(); // 清空预编译的sql
conn.close(); // 关闭数据库资源
System.out.println("成功修改"+ i +"行数据");
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
}
6.2 测试更新
com.cxk.test包下的 Test_Insert类
package com.cxk.test;
import com.cxk.demo.DemoUpdate;
import com.cxk.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
import static com.cxk.util.Conn.getConn;
public class Test_Update {
public static void main(String[] args) {
DemoUpdate demoUpdate = new DemoUpdate();
Scanner scanner = new Scanner(System.in);
System.out.println("请输入需要修改的ID");
String edit_id = scanner.next();
System.out.println("请输入新的ID");
String id = scanner.next();
System.out.println("请输入新的name");
String name = scanner.next();
User user = new User(id, name);
demoUpdate.update_one(user, edit_id);
}
}
6.删除Demo(Delete)
6.1 删除的方法
com.cxk.demo包下的 DemoDelete类
package com.cxk.demo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import static com.cxk.util.Conn.getConn;
public class DemoDelete {
// 根据主键单条删除
public int delete_one(String id) {
Connection conn = getConn(); // 获取数据库连接
int i = 0; // 受影响行数
String sql = "delete from user where Id='" + id + "'"; // sql语句
PreparedStatement pstmt; // 创建一个PreparedStatement用来构建sql
try {
pstmt = conn.prepareStatement(sql); // 预编译sql
i = pstmt.executeUpdate(); // 执行sql
pstmt.close(); // 清空预编译的sql
conn.close(); // 关闭数据库资源
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("成功删除"+ i + "条数据");
return i;
}
// 根据数据多条删除
public int delete_many(List<String> stringList) {
// 将String集合转化为以逗号分隔的字符串,满足sql语句的格式
StringBuffer stringBuffer = new StringBuffer();
stringList.stream().forEach(str->{
stringBuffer.append(str).append(",");
});
stringBuffer.deleteCharAt(stringBuffer.length()-1);
String str_in = stringBuffer.toString();
Connection conn = getConn(); // 获取数据库连接
int i = 0; // 受影响行数
String sql = "delete from user where id in (" + str_in + ")"; // sql语句
PreparedStatement pstmt; // 创建一个PreparedStatement用来构建sql
try {
pstmt = conn.prepareStatement(sql); // 预编译sql
i = pstmt.executeUpdate(); // 执行sql
pstmt.close(); // 清空预编译的sql
conn.close(); // 关闭数据库资源
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("成功删除"+ i + "条数据");
return i;
}
}
6.2 测试更新
com.cxk.test包下的 Test_Delete类
package com.cxk.test;
import com.cxk.demo.DemoDelete;
import com.cxk.model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.stream.Collectors;
import static com.cxk.util.Conn.getConn;
public class Test_Delete {
public static void main(String[] args) {
DemoDelete demoDelete = new DemoDelete();
Scanner scanner = new Scanner(System.in);
// 删除一条
System.out.println(" 请输入要删除的ID");
String id = scanner.next();
demoDelete.delete_one(id);
// 删除多条(以集合形式传参删除)
List<String> stringList = new ArrayList<>();
String s = "y";
while (s.equalsIgnoreCase("y")){
System.out.println(" 请输入要删除的ID");
String id2 = scanner.next();
stringList.add(id2);
System.out.println("是否继续输入? (请输入 Y or N)");
s = scanner.next();
}
demoDelete.delete_many(stringList);
}
}
总结
以上就是jdbc操作数据的增删改查的单条/多条的基本操作,代码demo已上传,jar包和sql语句文件也在代码demo中