JDBC简单的CRUD

JDBC进行简单的增删改查

首先建立要操作的数据库和表

• database: db
• table test2
  

搭环境

• 建个maven项目
• 导入依赖或者导包
  

database配置文件

建立两个包util、pojo和jdbc三个包

• util 用于建立工具类，封装重复性代码提高效率。
• pojo建立实体类。
• jdbc 用于建立增删改查demo

建立JDBCUtils

package com.cen.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author : cen
 * @date : 2020/5/17 14:56
 */
public class JDBCUtils {
//    设置全局变量
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
//        设置静态代码块，它随着类的加载而加载
    static {
        try {
//          加载配置文件
            Properties properties = new Properties();
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
//  编写获取对象的方法
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
//  释放资源
    public static void closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        try {
            if (connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (preparedStatement != null){
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (resultSet != null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

增加用户 insert

package com.cen.jdbc;

import com.cen.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;

/**
 * @author : cen
 * @date : 2020/5/17 15:49
 */
public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
//      获取连接对象
        connection = JDBCUtils.getConnection();
//      preparedStatement预编译sql语句
        String sql = "insert into test2(id,name,address) values(?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
//     设置要添加的数据
        preparedStatement.setInt(1,3);
        preparedStatement.setString(2,"参做");
        preparedStatement.setString(3,"永州");
//      执行增加命令
        int executeUpdate = preparedStatement.executeUpdate();
//      判断是否添加成功
        if(executeUpdate>0){
            System.out.println("添加成功");
        }
//      释放资源
        JDBCUtils.closeResource(connection,preparedStatement,null);
    }
}

删除用户 delete

package com.cen.jdbc;

import com.cen.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author : cen
 * @date : 2020/5/17 16:52
 */
public class TestDelete {
    public static void main(String[] args) throws SQLException {
//      定义Connection对象、PreparedStatement对象
        Connection connection = null;
        PreparedStatement preparedStatement = null;
//      获取连接数据库的对象
        connection = JDBCUtils.getConnection();
//      编写SQL语句和预编译SQL语句  
        String sql = "delete from test2 where id = ?";
        preparedStatement = connection.prepareStatement(sql);
//      设置删除用户的条件  
        preparedStatement.setInt(1,2);
//      执行删除命令
        int executeUpdate = preparedStatement.executeUpdate();
//      判断是否删除成功
        if(executeUpdate>0){
            System.out.println("插入成功");
        }
//      释放资源
        JDBCUtils.closeResource(connection,preparedStatement,null);

    }
}

修改用户 update

package com.cen.jdbc;

import com.cen.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author : cen
 * @date : 2020/5/17 16:41
 */
public class TestUpdate {
    public static void main(String[] args) throws SQLException {
//     获取Connecton对象PrepareStatement对象
        Connection connection = null;
        PreparedStatement preparedStatement = null;
//      获取数据库连接对象
        connection = JDBCUtils.getConnection();
//      编写SQL语句和预编译sql语句
        String sql = "update test2 set name = ? where id =?";
        preparedStatement = connection.prepareStatement(sql);
//      设置参数
        preparedStatement.setString(1,"星星");
        preparedStatement.setInt(2,2);
//      执行命令
        int executeUpdate = preparedStatement.executeUpdate();
//      判断成功与否
        if (executeUpdate>0){
            System.out.println("更新成功");
        }
//      释放资源
        JDBCUtils.closeResource(connection,preparedStatement,null);
    }
}

根据ID查询用户 select

package com.cen.jdbc;

import com.cen.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author : cen
 * @date : 2020/5/17 14:55
 */
public class TestSelectById {
    public static void main(String[] args) throws SQLException {
//      获取连接对象、预编译对象、结果集对象
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
//      获取连接
        connection = JDBCUtils.getConnection();
//      编写SQL语句和预编译SQL语句
        String sql = "select *from test2 where id=?";
        preparedStatement = connection.prepareStatement(sql);
//      设置参数
        preparedStatement.setInt(1,1);
//      执行查询命令
        resultSet = preparedStatement.executeQuery();
//      获取查询结果
        while (resultSet.next()){
            System.out.println(resultSet.getString("name"));
        }
//      释放资源
        JDBCUtils.closeResource(connection,preparedStatement,resultSet);
    }
}

执行结果

查询所有用户

package com.cen.jdbc;

import com.cen.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author : cen
 * @date : 2020/5/17 14:55
 */
public class TestSelect {
    public static void main(String[] args) throws SQLException {
//      获取连接对象、预编译对象、结果集对象
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
//      获取连接
        connection = JDBCUtils.getConnection();
//      编写SQL语句和预编译SQL语句
        String sql = "select *from test2";
        preparedStatement = connection.prepareStatement(sql);
//      执行查询命令
        resultSet = preparedStatement.executeQuery();
//      获取查询结果
        while (resultSet.next()){
            System.out.println("id:"+resultSet.getInt("id"));
            System.out.println("name:"+resultSet.getString("name"));
            System.out.println("address:"+resultSet.getString("address"));
        }
//      释放资源
        JDBCUtils.closeResource(connection,preparedStatement,resultSet);
    }
}

执行结果