JDBC实现增删改查

  首先建立起数据库连接

        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2、获取与数据库的连接
        String username = "root";
        String password = "123456";
        String url = "jdbc:mysql://localhost:3306/jdbcstudy";
        Connection conn = DriverManager.getConnection(url, username, password);

插入数据

Statement st = conn.createStatement();
String sql = "insert into 表名(字段1,字段2....) values(值1,值2...)";
int num = st.executeUpdate(sql);//num为改变的行数
if(num>0){
	System.out.println("插入成功")；
}

更改数据

Statement st = conn.createStatement();
String sql = "update 表名 set 字段 = '值' where 条件 ";
int num = st.executeUpdate(sql);
if(num>0){
	System.out.println("修改成功")；
}

删除数据

Statement st = conn.createStatament();
String sql = "delete from 表名 where 条件";
in num = st.executeUpdate(sql);
if(num>0){
	System.out.println("删除成功")；
}

查询数据

Statement st = conn.createStatament();
String sql = "select * from 表名 where 条件";
ResultSet rs = st.executeQuery(sql);
where(rs.next){
	//根据获取列的数据类型，调用rs相关方法映射到java对象中
}

对JDBC程序代码进行优化

  在我们编写上述那些代码多的时候，发现我们无论进行哪一步操作，都要写一些重复代码，例如加载数据，连接数据库，获取statement等等，所以我们优化程序代码，编写一个工具类专门来负责创建连接和关闭服务器资源。

  首先创建一个db.properties文件，将下面这些创建连接时所用放入其中

driver = com.mysql.jdbc.Driver
username = root
password = 123456
url = jdbc:mysql://localhost:3306/jdbcstudy?useSSL=true

  然后编写工具类，将创建连接数据库，释放资源等代码融入其中

public class JDBCUtils {
    private static String driver=null;
    private static String username=null;
    private static String password=null;
    private static String url=null;
    static{
        try {
            //加载配置文件
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(is);
            //读取配置文件
            driver  = properties.getProperty("driver");
            username  = properties.getProperty("username");
            password  = properties.getProperty("password");
            url  = properties.getProperty("url");

            //加载数据库驱动
            Class.forName(driver);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取数据库连接对象
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放资源
    public static void closeAll(ResultSet resultSet, Statement statement,Connection connection){
        if (resultSet!=null){
            try {
                resultSet.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();
            }
        }
    }
}

  然后用上面的工具类写个增删改查测试

public class Test1 {

    @Test
    public void insert() {
        Connection connection = null;
        Statement statement = null;
        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(5,'wangwu','123456','wangwu@sina.com','1979-12-04');";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); //返回受影响的行数

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }
    }

    @Test
    public void delete() {

        Connection connection = null;
        Statement statement = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "delete from users where id = 1";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); 
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }

    }

    @Test
    public void update() {

        Connection connection = null;
        Statement statement = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "update users set name = 'qinjiang' where id = 4";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); 
            if (i>0){
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }
    }

    @Test
    public void query() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(resultSet,statement,connection);
        }
    }
}