import java.sql.*;
import java.util.*;
public class BaseDao {
private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true";
private static String user = "root";
private static String pwd = "123456";
private static Connection conn;
private static PreparedStatement pstmt;
private static ResultSet rs;
// 1.加载驱动(静态代码块)
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2.建立连接
private static void getConn() {
if (conn == null) {
try {
conn = DriverManager.getConnection(url, user, pwd);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//读
public static ResultSet query(String sql, List<Object> params) {
getConn();
try {
pstmt = conn.prepareStatement(sql);
if (params != null && params.size() > 0) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
}
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
//写
public static int update(String sql, List<Object> params) {
int result = 0;
getConn();
try {
pstmt = conn.prepareStatement(sql);
if (params != null && params.size() > 0) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
}
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
return result;
}
//关闭连接
public static void close() {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//获取数据库所有表
public static List<String> getTables(){
getConn();
List<String> tables = new ArrayList<String>();
try {
DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet rs = dbMetaData.getTables(null, null, null,new String[] { "TABLE" });
while (rs.next()){
tables.add(rs.getString("TABLE_NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return tables;
}
//获取数据库某表的字段
public static List<String> getColumns(String table){
getConn();
List<String> columns = new ArrayList<String>();
String sql = "select * from " + table;
getConn();
try {
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
ResultSetMetaData meta = rs.getMetaData();
int columeCount = meta.getColumnCount();
for (int i = 1; i < columeCount + 1; i++) {
columns.add(meta.getColumnName(i));
}
} catch (SQLException e) {
e.printStackTrace();
}
return columns;
}
//测试
public static void main(String[] args) throws SQLException {
// String sql="insert into metric(kkey,value)values(?,?)";
// List<Object> params=new ArrayList<>();
//// params.add(1);
// params.add("66666");
// params.add("aaaaa5655aaaaaaaaaaaaaa");
// System.out.println(update(sql,params));
//查找
// String sql="select id,kkey,value,name from metric where name=?";
// List<Object> params=new ArrayList<>();
// params.add("process");
// ResultSet rs=query(sql,params);
// while (rs.next()){
// int id=rs.getInt("id");
// String key=rs.getString("kkey");
// String value=rs.getString("value");
// String nm=rs.getString("name");
//
// System.out.println("id:"+id+"name:"+nm+" key:"+key+" value:"+value);
// }
List<String> tables=getTables();
List<String> columns=getColumns("admin");
System.out.println("done");
}
}
Error:可能无法update,原因是因为sql语句书写不正确,正确的写法见下面(致谢)
//写2
public static int update2(String sql) {
int result = 0;
getConn();
try {
pstmt = conn.prepareStatement(sql);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
return result;
}
public String updateStatus(@RequestParam("id") String id, @RequestParam("field") String field, @RequestParam("state") String state,@RequestParam("table") String table) {
String sql = "update "+table+" set "+field+" = '"+state +"' where id='"+id+"'";
System.out.println(sql);
int rs=PatternDao.update2(sql);
System.out.println("rs:"+rs);
JSONObject jsonObject = new JSONObject();
jsonObject.put("msg", "");
if(rs>0)
jsonObject.put("code", 0);
else
jsonObject.put("code", -1);
return JSONObject.toJSONString(jsonObject);
}
jdbc:https://dev.mysql.com/downloads/connector/j/
ref:https://www.cnblogs.com/zhangdiIT/p/6772543.html#commentform