Java操作数据库主要有以下三个步骤:
1)查找数据库驱动;
2)建立数据库连接,并生成相应的连接对象Connection;
3)生成数据库操作对象stmtStatement,然后使用Statement对象进行数据库的增删改查操作。
注意:
1.增删改操作,使用executeUpdate()或者execute()提交,返回值为boolean类型。
2.查询操作,使用executeQuery(),返回为记录集ResultSet。
3.关闭时先关闭Statement对象再关闭Connection对象。
一、一类到底(以insert为例)
public class DBProces{
public static void main(String[] args) {
Connection conn = null;
//使用PreparedStatement防止sql注入
PreparedStatement st = null;
boolean rs = false;
String url = "jdbc:mysql://localhost:3306/db11?serverTimezone=UTC";
String userName = "root";
String password = "zxc0516..";
String driver = "com.mysql.cj.jdbc.Driver";
Class.forName(driver);
conn= DriverManager.getConnection(url,userName,password);
String sql = "INSERT INTO `check`(`name`,checkTime,checkType) VALUES(?,?,?)";
try {
st=conn.prepareStatement(sql);
st.setString(1,check.getName());
st.setString(2,check.getCheckTime());
st.setString(3,check.getCheckType());
rs=st.execute();
// while (rs.next()){
// System.out.println(rs.getInt("userId"));
// System.out.println(rs.getString("userName"));
// System.out.println(rs.getString("userPwd"));
// }
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(st!=null){
st.close();
}
if(conn!=null){
conn.close();
}
}
}
}
二、为方便操作,将上述操作封装为工具类
数据库配置文件db.propertis
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db11?serverTimezone=UTC
username=root
password=zxc0516..
具体数据库操作类
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class DBUtils {
private Connection conn = null;
private Statement stmt = null;
public DBUtils(){
init();
}
public void init(){
try{
String driver="";//数据库驱动类
String url="";
String username="";
String password="";
//读配置文件,获取,driver,url,username,password
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
try {
prop.load(in);
} catch (IOException e) {
e.printStackTrace();
}
driver = prop.getProperty("driver");
url=prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
//1.查找数据库驱动
Class.forName(driver);//查找数据库驱动
//2.建立数据库连接,并生成相应的连接对象
conn = DriverManager.getConnection(url,username,password);
//3.生成数据库操作对象stmt
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
}catch(Exception e){
e.printStackTrace();
}
}
//在数据表中添加,修改,删除一条记录
public boolean update(String sql){
try{
stmt.executeUpdate(sql);
return true;
}catch(Exception e){
e.printStackTrace();
return false;
}
}
//查询结果
public ResultSet query(String sql){
try{
return stmt.executeQuery(sql);
}catch(Exception ex){
ex.printStackTrace();
return null;
}
}
public void close(){
try{
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
测试
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
@org.junit.jupiter.api.Test
public void queryTest(){
DBProces dbProces = new DBProces();
String sql = "select * from `check`";
ResultSet rt = dbProces.query(sql);
while (true){
try {
while (rt.next()){
System.out.println(rt.getInt("id"));
System.out.println(rt.getString("name"));
System.out.println(rt.getString("checkType"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@org.junit.jupiter.api.Test
public void updateTest(){
DBProces dbProces = new DBProces();
String sql = "delete from `check` where id= 4";
boolean bool = dbProces.update(sql);
if(bool){
System.out.println("delete成功");
}else{
System.out.println("delete失败");
}
}
}