本文介绍如何使用JDBC连接MySQL数据库，并实现基本的增删改查操作。文章提供了完整的示例代码，包括连接数据库、执行SQL语句以及处理结果集等步骤。
年末，公司比较闲，再重新整理一遍，以下都是自己动手实践过且运行成功
不管框架使用起来是如何的方便，提高多少的开发效率，但是都是从基础之上开始的，就像房子的地基
正文：
使用JDBC连接MySQL完成增删改查，使用User类作为对应表的实体类来操作
当然，首先需要下载jdbc的jar并添加到对应工程
比较规范的连接和使用数据库
package TestJDBC;
import java.sql.*;
public class SimpleInsert {
// JDBC 驱动名及数据库 URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/test";
// 数据库的用户名与密码，需要根据自己的设置
static final String USERNAME = "admin";
static final String PASSWORD = "123456";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD);
// 执行查询
System.out.println(" 实例化Statement对象...");
stmt = conn.createStatement();
//两种插入都可以实现，数据库里正常显示
/*String sql1 = "INSERT INTO user VALUES(7,'夏历六号','xlll')";
stmt.execute(sql1);*/
/*PreparedStatement stmt2 = conn.prepareStatement("INSERT INTO user VALUES(3,'夏历二号','xl')");
stmt2.executeUpdate();*/
String sql;
sql = "SELECT id, name, password FROM user";
ResultSet rs = stmt.executeQuery(sql);
// 展开结果集数据库
while(rs.next()){
// 通过字段检索
int id = rs.getInt("id");
String name = rs.getString("name");
String url = rs.getString("password");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 名字: " + name);
System.out.print(", 密码: " + url);
System.out.print("\n");
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
// 处理 JDBC 错误
se.printStackTrace();
}catch(Exception e){
// 处理 Class.forName 错误
e.printStackTrace();
}finally{
// 关闭资源
try{
if(stmt!=null) stmt.close();
}catch(SQLException se2){
}// 什么都不做
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
User类
package TestJDBC;
public class User {
private int id;
private String name;
private String password;
private String phone;
public User(){
}
public User(int id, String name, String password){
this.id = id;
this.name = name;
this.password = password;
}
public User(int id, String name, String password, String phone){
this.id = id;
this.name = name;
this.password = password;
this.phone = phone;
}
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 String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
增删改查
package TestJDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class JdbcForUser {
static final String DRIVER = "com.mysql.jdbc.Driver";
static final String URL = "jdbc:mysql://localhost:3306/test";
static final String USER_NAME = "admin";
static final String PASSWORD = "123456";
public static void main(String[] args) throws Exception{
JdbcForUser jfu = new JdbcForUser();
jfu.addUser(new User(3,"oo","oo","333"));
jfu.addUser(new User(4,"uu","uu","444"));
User user = jfu.getById(2);
System.out.println(user.getName()+" "+user.getPhone());
jfu.updateUser(new User(4,"ou","ou","444"));
List<User> users = new ArrayList<User>();
users = jfu.getAll();
for(User u : users){
System.out.println(u.getName());
}
List<Integer> ids = new ArrayList<Integer>();
ids.add(3);
ids.add(4);
jfu.betchDelUser(ids);
}
//获取连接方法
public static Connection getConnection() throws Exception{
Class.forName(DRIVER);
Connection cnt = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
return cnt;
}
//增
public void addUser(User user) throws Exception{
Connection conn = JdbcForUser.getConnection();
String sql = " INSERT INTO user (id,name,password,phone) VALUES (?,?,?,?) ";
PreparedStatement stmt = conn.prepareStatement(sql);//预编译sql
stmt.setInt(1, user.getId());
stmt.setString(2, user.getName());
stmt.setString(3, user.getPassword());
stmt.setString(4, user.getPhone());
stmt.execute();
}
//删（根据id 一条记录）
public void delUser(int id) throws Exception{
Connection conn = JdbcForUser.getConnection();
String sql = " DELETE FROM user WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, id);
stmt.execute();
}
//批量删除
public void betchDelUser(List<Integer> ids) throws Exception{
Connection conn = JdbcForUser.getConnection();
String sql = " DELETE FROM user WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
for(Integer i : ids){
stmt.setInt(1, i);
stmt.execute();
}
}
//改
public void updateUser(User user) throws Exception{
Connection conn = JdbcForUser.getConnection();
String sql = " UPDATE user SET name = ?, password = ?, phone =? WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, user.getName());
stmt.setString(2, user.getPassword());
stmt.setString(3, user.getPhone());
stmt.setInt(4, user.getId());
stmt.execute();
}
//查，单条
public User getById(int id) throws Exception{
Connection conn = JdbcForUser.getConnection();
String sql = " SELECT * FROM user WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, id);
ResultSet rs = stmt.executeQuery();
User user = new User();
while(rs.next()){
//user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
//user.setPassword(rs.getString("password"));
user.setPhone(rs.getString("phone"));
}
return user;
}
//查询全部
public List<User> getAll() throws Exception{
Connection conn = JdbcForUser.getConnection();
String sql = " SELECT * FROM user";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
List<User> users = new ArrayList<User>();
User user = null;
while(rs.next()){
user = new User();//需要在这里new，在上面new出来在这里使用，查询出来的会是同一个user，即同一条记录
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setPhone(rs.getString("phone"));
users.add(user);
}
return users;
}
}
——————over——————
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
