简单操作的例子
public class demo {
public static void main(String[] args) {
try {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/demo?useSSL=false";
String username = "root";
String password = "123456";
//2、获得连接
Connection connection = DriverManager.getConnection(url, username, password);
//3、准备sql语句
String sql = "insert into user_info (username, password, email, is_admin) values ('adc', '123', 'abc@csu.edu.cn',0)";
//4、执行sql语句 有两个执行对象 Statement PreparedStatement
Statement statement = connection.createStatement();
//5、获得结果
int result = statement.executeUpdate(sql);
//6、判断结果是否为成功
if(result == 1){
System.out.println("执行成功!");
}
//7、关闭获取过的对象
statement.close();
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
所操作的表
具体的流程
流程说明:设计一个JDBC对数据库进行增删改查操作(包涵POJO、DBUil、DAO)
POJO层存放对象
package jdbc;
//POJO
public class User {
private int id;
private String username;
private String password;
private String email;
private boolean admin;
public User(){
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public boolean isAdmin() {
return admin;
}
public void setAdmin(boolean admin) {
this.admin = admin;
}
}
DBUtil层存放公共代码,封装数据库连接操作
package jdbc;
import java.sql.*;
//DBUtil封装对数据库连接操作
//工具类,jdbc中专门用来写公共代码,就比如说连接数据库
public class DBUtil {
//写成静态的,其他的方法不能进行修改
//驱动的位置
private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
//数据库连接URL,不同版本的mysql这里可能有所不同, 其中的demo是你要连接的数据库的名字
private static final String URL = "jdbc:mysql://127.0.0.1:3306/demo?useSSL=false";
//这里的账户和密码,因人而异
private static final String USERNAME = "root";
private static final String PASSWORD = "123456";
//获取数据库连接
public static Connection getConnection() throws Exception{
//加载驱动
Class.forName(DRIVER_CLASS);
//获取数据库连接
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
//返回连接
return connection;
}
//关闭数据库连接
public static void closeConnection(Connection connection) throws Exception{
if(connection != null){
connection.close();
}
}
//关闭执行对象Statement
public static void closeStatement(Statement statement) throws Exception{
if(statement != null){
statement.close();
}
}
//关闭执行对象PreparedStatement
public static void closePreparedStatement(PreparedStatement preparedStatement) throws Exception{
if(preparedStatement != null){
preparedStatement.close();
}
}
//关闭结果集
public static void closeResultSet(ResultSet resultSet) throws Exception{
if(resultSet != null){
resultSet.close();
}
}
//测试一下
// public static void main(String[] args) {
// try{
// Connection connection = getConnection();
// closeConnection(connection);
// }catch (Exception e){
// e.printStackTrace();
// }
// }
}
DAO层封装对表的增删改查功能
package jdbc;
import com.sun.jdi.event.ExceptionEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
//DAO封装对表的操作,用于对User数据表进行增删改查
public class UserDAO {
//写成静态方便修改 sql语句操作关键字一般大写,表名小写
private static final String INSERT_USER = "INSERT INTO user_info (username, password, email, is_admin) VALUES (?,?,?,?)";
//根据id删除
private static final String DELETE_USER = "DELETE FROM user_info WHERE id = ?";
//根据id修改数据
private static final String UPDATE_USER = "UPDATE user_info SET username = ?,password = ?, email = ?,is_admin = ? WHERE id = ?";
//查询表中所有数据
private static final String SELECT_ALL = "SELECT * FROM user_info";
//新增一个用户
public int insertUser(User user){
//设置int返回值,用于判断操作是否成功
int result = 0;
try{
Connection connection = DBUtil.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USER);
//这里的1,2,3,4所放的东西的顺序分别是问号的所表示东西的顺序
preparedStatement.setString(1, user.getUsername());
preparedStatement.setString(2, user.getPassword());
preparedStatement.setString(3, user.getEmail());
preparedStatement.setInt(4, user.isAdmin()?0 : 1);
result = preparedStatement.executeUpdate();
DBUtil.closePreparedStatement(preparedStatement);
DBUtil.closeConnection(connection);
}catch (Exception e){
e.printStackTrace();
}
return result;
}
//根据id删除用户
public int deleteUser(User user){
//设置int返回值,用于判断操作是否成功
int result = 0;
try{
Connection connection = DBUtil.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USER);
preparedStatement.setInt(1, user.getId());
result = preparedStatement.executeUpdate();
DBUtil.closePreparedStatement(preparedStatement);
DBUtil.closeConnection(connection);
}catch (Exception e){
e.printStackTrace();
}
return result;
}
//根据id更新用户信息
public int updateUser(User user){
//设置int返回值,用于判断操作是否成功
int result = 0;
try{
Connection connection = DBUtil.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USER);
//这里的1,2,3,4所放的东西的顺序分别是问号的所表示东西的顺序
preparedStatement.setString(1, user.getUsername());
preparedStatement.setString(2, user.getPassword());
preparedStatement.setString(3, user.getEmail());
preparedStatement.setInt(4, user.isAdmin()?0 : 1);
preparedStatement.setInt(5,user.getId());
result = preparedStatement.executeUpdate();
DBUtil.closePreparedStatement(preparedStatement);
DBUtil.closeConnection(connection);
}catch (Exception e){
e.printStackTrace();
}
return result;
}
//查询表中所有数据
public List<User> selectAll(){
List<User> userList = new ArrayList<>();
try{
Connection connection = DBUtil.getConnection();
//查询所有数据不需要参数数 不需要使用PreparedStatement操作对象 使用Statement即可
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(SELECT_ALL);
while(resultSet.next()){
User user = new User();
user.setId(resultSet.getInt(1));
user.setUsername(resultSet.getString(2));
user.setPassword(resultSet.getString(3));
user.setEmail(resultSet.getString(4));
user.setAdmin(resultSet.getInt(5) == 0);
userList.add(user);
}
DBUtil.closeResultSet(resultSet);
DBUtil.closeStatement(statement);
DBUtil.closeConnection(connection);
}catch (Exception e){
e.printStackTrace();
}
return userList;
}
public static void main(String[] args){
//测试insertUser()方法是否成功
// User user = new User();
// user.setUsername("aaa");
// user.setPassword("123");
// user.setEmail("aaa@csu.edu.cn");
// user.setAdmin(false);
//
// int result = new UserDAO().insertUser(user);
// System.out.println(result);//返回1就说明添加成功啦
//测试selectAll()是否成功
List<User> userList = new UserDAO().selectAll();
for(User user : userList){
System.out.println(user.getId() + "," + user.getUsername() + "," + user.getPassword() + "," + user.getEmail() + "," + user.isAdmin());
}
}
}