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);
}
}
执行结果