package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtil {
private static final String DB_USER_NAME = "root";
private static final String DB_PASSWORD ="root";
private static final String DB_NAME = "hello_world";
private static final String IP = "127.0.0.1";
private static final String URL="jdbc:mysql://"+IP+":3306/"+DB_NAME;
private static Connection conn;
private static Statement stmt;
private static ResultSet rs;
private static Connection getCon() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
if(conn == null || conn.isClosed()){
conn = DriverManager.getConnection(URL, DB_USER_NAME, DB_PASSWORD);
}
return conn;
}
private static Statement openStmt() throws ClassNotFoundException, SQLException{
stmt = getCon().createStatement();
return stmt;
}
public static int executeSQL(String sql){
int i = 0;
try {
i = openStmt().executeUpdate(sql);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
close();
}
return i;
}
public static ResultSet search(String sql){
try {
rs = openStmt().executeQuery(sql);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static void close(){
try {
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
测试
package test;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import org.junit.Test;
import util.JDBCUtil;
public class SQLDemo {
@Test
public void testInsert(){
String sql = "insert into goods values(default,'可口可乐',3.5,'2021-09-01',100)";
int i = JDBCUtil.executeSQL(sql);
System.out.println(i);
}
@Test
public void testUpdate(){
String sql = "update goods set g_price=2.5 where g_name='可口可乐'";
int i = JDBCUtil.executeSQL(sql);
System.out.println(i);
}
@Test
public void testDelete(){
String sql = "delete from goods where g_name = '可口可乐'";
int i = JDBCUtil.executeSQL(sql);
System.out.println(i);
}
@Test
public void testSelect() throws SQLException{
String sql = "select * from goods";
ResultSet rs = JDBCUtil.search(sql);
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("g_name");
double price = rs.getDouble("g_price");
Date date = rs.getDate("g_date");
int cnt = rs.getInt("cnt");
System.out.println("id:"+id+"商品名:"+name+"价格:"+price+"生产日期:"+date+"库存:"+cnt);
}
JDBCUtil.close();
}
}