原生java連接mysql還是比較麻煩的，所以我們還是很有必要把連接數(shù)據(jù)庫及操作數(shù)據(jù)庫表的方法進行封裝，這樣就會容易很多
這里做一個簡單的封裝，方便大家學(xué)習(xí)使用
public class ConnDB {
// MySQL 8.0 以下版本 - JDBC 驅(qū)動名及數(shù)據(jù)庫 URL
//static final String DRIVER = "com.mysql.jdbc.Driver";
//static final String DB_URL = "jdbc:mysql://localhost:3306/info0918";
// MySQL 8.0 以上版本 - JDBC 驅(qū)動名及數(shù)據(jù)庫 URL
static final String DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/ncycc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
static final String USER = "root";
static final String PASS = "root";
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
public ConnDB(){
try {
Class.forName(DRIVER); //將mysql驅(qū)動注冊到DriverManager中去
conn = DriverManager.getConnection(DB_URL,USER,PASS);
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//基本的增,刪,改操作
public int exec(String sql, Object...args) {
int count = 0;
try {
//prepareStatement對象防止sql注入的方式是把用戶非法輸入的單引號用\反斜杠做了轉(zhuǎn)義，從而達到了防止sql注入的目的
ps = conn.prepareStatement(sql);
//設(shè)置占位符參數(shù)
if(args != null) {
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
}
count = ps.executeUpdate();
//設(shè)置占位符參數(shù)
}
catch(SQLException e) {
System.out.println(e.getMessage());
}
return count;
}
//返回新增加數(shù)據(jù)的id
public int lastInsertId(String sql, Object...args) {
int id = -1;
try {
//prepareStatement對象防止sql注入的方式是把用戶非法輸入的單引號用\反斜杠做了轉(zhuǎn)義，從而達到了防止sql注入的目的
ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//設(shè)置占位符參數(shù)
if(args != null) {
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
}
ps.executeUpdate();
rs = ps.getGeneratedKeys();
if(rs.next())
{
id = rs.getInt(1);
}
//設(shè)置占位符參數(shù)
}
catch(SQLException e) {
System.out.println(e.getMessage());
}
return id;
}
//查詢操作
public ResultSet fetch(String sql, Object...args) {
try {
ps = conn.prepareStatement(sql);
//設(shè)置占位符參數(shù)
if(args != null) {
for(int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
}
rs = ps.executeQuery(); //返回 查詢的數(shù)據(jù)結(jié)果
//設(shè)置占位符參數(shù)
}
catch(SQLException e) {
System.out.println(e.getMessage());
}
return rs;
}
public void close() {
try {
if(rs != null) {rs.close(); rs = null;}
if(ps != null) {ps.close(); ps = null;}
if(conn != null) {conn.close(); conn = null;}
}
catch(SQLException e) {
System.out.println(e.getMessage());
}
}
}使用方法如下：
ConnDB conn = new ConnDB();
String sql = "update webconfig set post = ? where id = ?";
int num = conn.exec(sql,"88888886",1);
sql = "select * from webconfig where id = ?";
ResultSet rs = conn.fetch(sql,1);
try{
if(rs.next()){
System.out.println(rs.getString("address"));
}
}
catch (SQLException e){
System.out.println(e.getMessage());
}
finally {
conn.close();
}