jdbc动态建表、插入记录、查询等功能（mysql）

最近遇到一个项目，需要使用jdbc判断数据库中table是否存在，并实现动态的创建，添加记录和相关的查询，自己经过学习，实现了这个功能，并可以导入为jar包，动态的使用。

一：实现数据库的连接（通过配置文件实现）

package com.delta.smarthome.utils;


import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;


public class JDBCUtil {
public static Connection getConnection() throws Exception{
       String driverClass=null;
       String jdbcUrl=null;
       String user=null;
       String pwd=null;
       InputStream in = new FileInputStream("src/config.properties");
       Properties properties=new Properties();
       properties.load(in);         
       driverClass=properties.getProperty("driver");      
       jdbcUrl=properties.getProperty("url");
       user=properties.getProperty("user");     
       pwd=properties.getProperty("pwd");
       Driver driver=(Driver) Class.forName(driverClass).newInstance();
       Properties info=new Properties();
       info.put("user",user);
       info.put("password",pwd);
       Connection connection=driver.connect(jdbcUrl, info);
       return connection;
   }
public static void close(Connection conn,PreparedStatement ps, Statement st, ResultSet rs) {

if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if (ps != null) {
try {
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (Exception e) {
e.printStackTrace();
}
}


if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}


@SuppressWarnings("static-access")
public static void main(String[] args) {
JDBCUtil jdbcUtil=new JDBCUtil();
try {
System.out.println(jdbcUtil.getConnection());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
   }
}

二：数据库动态操作类（在该类中实现了数据库中表是否存在的判断，动态建表，插入数据，动态查询，分页查询等，这些功能只需要你提供表名，表的相关字段属性，就可以自动实现）

package com.delta.smarthome.sql;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


import com.delta.smarthome.utils.JDBCUtil;
import com.mysql.jdbc.Statement;


public class DatabaseOperations {
public static Connection conn = null;
public static PreparedStatement ps = null;


/**
* 判断表是否存在
* 
* @param tabName
* @return
* @throws SQLException
*/
public static boolean exitTable(String tabName) {
ResultSet resultSet = null;
Statement stmt = null;
boolean flag = false;
try {
conn = JDBCUtil.getConnection();
conn.setAutoCommit(false);
stmt = (Statement) conn.createStatement();
String sql = "show tables like '" + tabName + "';";
resultSet = stmt.executeQuery(sql);
if (resultSet.next()) {
flag = true;
}
conn.commit();
} catch (SQLException e) {
System.out.println("查询失败：" + e.getMessage());
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("回滚失败：" + e1.getMessage());
}


} catch (Exception e2) {
System.out.println("查询失败" + e2.getMessage());
} finally {
// 关闭数据库连接
JDBCUtil.close(conn, ps, stmt, resultSet);
}
return flag;
}


/**
* 创建表
* 
* @param tabName
*            表名称
* @param tab_fields
*            表字段
* @throws Exception
*/
public static void createTable(String tabName, String[] tab_fields) {
try {
conn = JDBCUtil.getConnection(); // 首先要获取连接，即连接到数据库
conn.setAutoCommit(false);
String sql = "create table " + tabName
+ "(id int auto_increment primary key not null";
if (tab_fields != null && tab_fields.length > 0) {
sql += ",";
int length = tab_fields.length;
for (int i = 0; i < length; i++) {
// 添加字段
sql += tab_fields[i].trim() + " varchar(50)";
// 防止最后一个,
if (i < length - 1) {
sql += ",";
}
}
}
// 拼凑完 建表语句 设置默认字符集
sql += ",saveTime varchar(64) DEFAULT NULL)DEFAULT CHARSET=utf8;";
System.out.println("添加数据的sql:" + sql);
ps = conn.prepareStatement(sql);
ps.executeUpdate(sql);
conn.commit();
} catch (SQLException e) {
System.out.println("建表失败" + e.getMessage());
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("回滚失败：" + e1.getMessage());
}


} catch (Exception e2) {
System.out.println("建表失败" + e2.getMessage());
} finally {
// 关闭数据库连接
JDBCUtil.close(conn, ps, null, null);


}
}


/**
* 添加数据
* 
* @param tabName
*            表名
* @param fields
*            参数字段
* @param data
*            参数字段数据
* @throws Exception
*/
public static void insert(String tabName, String[] fields, String[] data) {


try {
conn = JDBCUtil.getConnection(); // 首先要获取连接，即连接到数据库
conn.setAutoCommit(false);
String sql = "insert into " + tabName + "(";
int length = fields.length;
for (int i = 0; i < length; i++) {
sql += fields[i];
// 防止最后一个,
if (i < length - 1) {
sql += ",";
}
}
sql += ",saveTime) values(";
for (int i = 0; i < length; i++) {
sql += "?";
// 防止最后一个,
if (i < length - 1) {
sql += ",";
}
}
sql += ",now());";
System.out.println("添加数据的sql:" + sql);
// 预处理SQL 防止注入
excutePs(sql, length, data);
// 执行
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
System.out.println("添加数据失败" + e.getMessage());
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("回滚失败：" + e1.getMessage());
}
} catch (Exception e2) {
System.out.println("添加数据失败" + e2.getMessage());
} finally {
JDBCUtil.close(conn, ps, null, null);


}
}


/**
* 查询表 【查询结果的顺序要和数据库字段的顺序一致】
* 
* @param tabName
*            表名
* @param fields
*            参数字段
* @param data
*            参数字段数据
* @param tab_fields
*            获得的数据库的字段
*/
public static List<String[]> query(String tabName, String[] fields,
String[] data, String[] tab_fields) {
String[] tab_field = { "saveTime" };
int strLen1 = tab_fields.length;// 保存第一个数组长度
int strLen2 = tab_field.length;// 保存第二个数组长度
tab_fields = Arrays.copyOf(tab_fields, strLen1 + strLen2);// 扩容
System.arraycopy(tab_field, 0, tab_fields, strLen1, strLen2);// 将第二个数组与第一个数组合并


List<String[]> list = new ArrayList<String[]>();
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection(); // 首先要获取连接，即连接到数据库
conn.setAutoCommit(false);
String sql = "select * from  " + tabName + " where ";
int length = fields.length;
for (int i = 0; i < length; i++) {
sql += fields[i] + " = ? ";
// 防止最后一个,
if (i < length - 1) {
sql += " and ";
}
}
sql += ";";
System.out.println("查询sql:" + sql);
// 预处理SQL 防止注入
excutePs(sql, length, data);
// 查询结果集
rs = ps.executeQuery();
// 存放结果集
while (rs.next()) {
String[] result = new String[tab_fields.length];
for (int i = 0; i < tab_fields.length; i++) {
result[i] = rs.getString(tab_fields[i]);
}
list.add(result);
}
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("回滚失败" + e1.getMessage());
}
System.out.println("查询失败" + e.getMessage());
} catch (Exception e2) {
System.out.println("查询失败" + e2.getMessage());
} finally {
JDBCUtil.close(conn, ps, null, rs);


}
return list;
}


/**
* 后台分页显示
* 
* @param tabName
* @param pageNo
* @param pageSize
* @param tab_fields
* @return
*/
public static List<String[]> queryForPage(String tabName, String[] fields,
String[] data, String[] tab_fields, int pageNo, int pageSize) {
String[] tab_field = { "saveTime" };
int strLen1 = tab_fields.length;// 保存第一个数组长度
int strLen2 = tab_field.length;// 保存第二个数组长度
tab_fields = Arrays.copyOf(tab_fields, strLen1 + strLen2);// 扩容
System.arraycopy(tab_field, 0, tab_fields, strLen1, strLen2);// 将第二个数组与第一个数组合并
List<String[]> list = new ArrayList<String[]>();
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
conn.setAutoCommit(false);
String sql = "select * from  " + tabName + " where ";
int length = fields.length;
for (int i = 0; i < length; i++) {
sql += fields[i] + " = ? ";
// 防止最后一个,
if (i < length - 1) {
sql += " and ";
}
}
sql += "LIMIT ?,? ;";
System.out.println("查询sql:" + sql);
// 预处理SQL 防止注入
ps = conn.prepareStatement(sql);
// 注入参数


for (int i = 0; i < data.length; i++) {
ps.setString(i + 1, data[i]);
}
ps.setInt((data.length) + 1, pageNo);
ps.setInt((data.length) + 2, pageSize);
rs = ps.executeQuery();
// 存放结果集
while (rs.next()) {
String[] result = new String[tab_fields.length];
for (int i = 0; i < tab_fields.length; i++) {
result[i] = rs.getString(tab_fields[i]);
}
list.add(result);
}
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("回滚失败" + e1.getMessage());
}
System.out.println("查询失败" + e.getMessage());
} catch (Exception e1) {
System.out.println("查询失败" + e1.getMessage());
} finally {
JDBCUtil.close(conn, ps, null, rs);


}
return list;
}


/**
* 获取某张表总数
* 
* @param tabName
* @return
* @throws Exception
*/
public static Integer getCount(String tabName) {
int count = 0;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
conn.setAutoCommit(false);
String sql = "select count(*) from  " + tabName + " ;";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
count = rs.getInt(1);
}
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("回滚失败" + e1.getMessage());
}
System.out.println("获取总数失败" + e.getMessage());
} catch (Exception e) {


} finally {
JDBCUtil.close(conn, ps, null, rs);


}
return count;
}


/**
* 清空表数据
* 
* @param tabName
*            表名称
*/
public static void delete(String tabName) {


try {
conn = JDBCUtil.getConnection();
conn.setAutoCommit(false);
String sql = "delete from  " + tabName + ";";
System.out.println("删除数据的sql:" + sql);
// 预处理SQL 防止注入
ps = conn.prepareStatement(sql);
// 执行
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("回滚失败" + e1.getMessage());
}
System.out.println("删除数据失败" + e.getMessage());
} catch (Exception e1) {
// TODO 自动生成 catch 块
e1.printStackTrace();
} finally {
JDBCUtil.close(conn, ps, null, null);


}
}


/**
* 用于注入参数
* 
* @param ps
* @param data
* @throws SQLException
*/
private static void excutePs(String sql, int length, String[] data)
throws SQLException {
// 预处理SQL 防止注入
ps = conn.prepareStatement(sql);
// 注入参数
for (int i = 0; i < length; i++) {
ps.setString(i + 1, data[i]);
}
}


/**
* 删除数据表 如果执行成功则返回false
* 
* @param tabName
* @return
*/
// public static boolean dropTable(String tabName) {
// boolean flag = true;
// try {
// conn = JDBCUtil.getConnection();
// } catch (Exception e1) {
// // TODO 自动生成 catch 块
// e1.printStackTrace();
// } // 首先要获取连接，即连接到数据库
// try {
// String sql = "drop table  " + tabName + ";";
// // 预处理SQL 防止注入
// ps = conn.prepareStatement(sql);
// // 执行
// flag = ps.execute();
// // 关闭流
// ps.close();
// conn.close(); // 关闭数据库连接
// } catch (SQLException e) {
// System.out.println("删除数据失败" + e.getMessage());
// }
// return flag;
// }


}

三：测试主函数

package com.delta.test;


import java.util.List;


import com.delta.smarthome.sql.DatabaseOperations;


public class Test {
public static void main(String[] arg) throws Exception{
String[] str={"name","data"};
String[] data={"1","1"};
String table="a";
String[] strf={"name"};
String[] dataf={"1"};

//判断表是否存在
boolean flag=false;

flag=DatabaseOperations.exitTable(table);




if(flag){
System.out.println("表已存在");
//插入信息
DatabaseOperations.insert(table,str,data);
//查询
List<String[]> a=DatabaseOperations.query(table,strf,dataf,str);
for(int i=0;i<a.size();i++){
System.out.println(a.get(i)[0]+a.get(i)[1]+a.get(i)[2]);
}
//查询记录总数
System.out.println(DatabaseOperations.getCount(table));
//分页查询
List<String[]> b=DatabaseOperations.queryForPage(table,strf,dataf,str,2,3);
for(int i=0;i<b.size();i++){
System.out.println(b.get(i)[0]+b.get(i)[1]+b.get(i)[2]);
}



}else {
System.out.println("表不存在");
//建表

DatabaseOperations.createTable(table,str);

}



}


}

四：数据库配置

driver=com.mysql.jdbc.Driver


url=jdbc:mysql://localhost:3306/cornerstone


user=root


pwd=root