Q1：实体层（vo）
package com;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/**
* 根据数据库表结构 自动生成java Bean
*
* @author DLHT 2016年3月4日下午5:00:28 AutoCreateClass.java DLHT
*/
public class AutoCreateBean {
/*// sqlserver 驱动类
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
// 数据库登录用户名
private static final String USER = "sa";//数据库中的账号
// 数据库登录密码
private static final String PASSWORD = "";//数据库中的密码
// 数据库连接地址
private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=TakeawayDB";*/
//mysql驱动类
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/schooldb?serverTimezone=GMT%2B8";
private static final String USER = "root";//数据库中的账号
private static final String PASSWORD = "";//数据库中的密码
private static String tablename;
private String[] colnames; // 列名数组
private String[] colTypes; // 列名类型数组
private int[] colSizes; // 列名大小数组
private boolean f_util = false; // 是否需要导入包java.util.*
private boolean f_sql = false; // 是否需要导入包java.sql.*
/**
* 获取指定数据库中包含的表 TBlist
*
* @time 2016年3月4日下午5:54:52
* @packageName com.util
* @return 返回所有表名(将表名放到一个集合中)
* @throws Exception
*/
public List<String> TBlist() throws Exception {
// 访问数据库 采用 JDBC方式
Class.forName(DRIVER);
Connection con = DriverManager.getConnection(URL, USER, PASSWORD);
DatabaseMetaData md = con.getMetaData();
List<String> list = null;
//注意这是特别需要注意的点
ResultSet rs = md.getTables(con.getCatalog(), null, "%", null);//mysql需要用到的
//ResultSet rs = md.getTables(null, null, null, null);//sqlserver需要用到的
if (rs != null) {
list = new ArrayList<String>();
}
while (rs.next()) {
//System.out.println("|表" + (i++) + ":" + rs.getString("TABLE_NAME"));
String tableName = rs.getString("TABLE_NAME");
list.add(tableName);
}
rs = null;
md = null;
con = null;
return list;
}
public void GenEntity(List<String> TBlist, String packageName)throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSetMetaData rsmd = null;
// 访问数据库 采用 JDBC方式
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
for (int k = 0; k < TBlist.size(); k++) {
tablename = TBlist.get(k);
String strsql = "select * from " + tablename;
pstmt = conn.prepareStatement(strsql);
rsmd = pstmt.getMetaData();
int size = rsmd.getColumnCount();
// 共有多少列
colnames = new String[size];
colTypes = new String[size];
colSizes = new int[size];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
colnames[i] = rsmd.getColumnName(i + 1);
colTypes[i] = rsmd.getColumnTypeName(i + 1);
if (colTypes[i].equalsIgnoreCase("datetime")) {
f_util = true;
}
if (colTypes[i].equalsIgnoreCase("image")
|| colTypes[i].equalsIgnoreCase("text")) {
f_sql = true;
}
colSizes[i] = rsmd.getColumnDisplaySize(i + 1);
}
markerBean(initcap(tablename), parse(), packageName);
}
pstmt = null;
rsmd = null;
conn = null;
}
/**
* 解析处理(生成实体类主体代码)
*/
private String parse() {
StringBuffer sb = new StringBuffer();
if (f_util) {
sb.append("import java.util.Date;\r\n");
}
if (f_sql) {
sb.append("import java.sql.*;\r\n\r\n\r\n");
}
sb.append("public class " + initcap(tablename) + " {\r\n");
processAllAttrs(sb);
processConstructor(sb,initcap(tablename));
processAllMethod(sb);
sb.append("}\r\n");
return sb.toString();
}
/**
* 创建java 文件 将生成的属性 get/set 方法 保存到 文件中 markerBean
*
* @time 2015年9月29日下午4:15:22
* @packageName fanshe
* @param className
* 类名称
* @param content
* 类内容 包括属性 getset 方法
*/
public void markerBean(String className, String content, String packageName) {
String folder = System.getProperty("user.dir") + "/src/" + packageName + "/";
File file = new File(folder);
if (!file.exists()) {
file.mkdirs();
}
String fileName = folder + className + ".java";
try {
File newdao = new File(fileName);
FileWriter fw = new FileWriter(newdao);
fw.write("package\t" + packageName.replace("/", ".") + ";\r\n");
fw.write(content);
fw.flush();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 生成所有的方法
*
* @param sb
*/
private void processAllMethod(StringBuffer sb) {
for (int i = 0; i < colnames.length; i++) {
sb.append("\tpublic void set" + initcap(colnames[i]) + "("
+ sqlType2JavaType(colTypes[i]) + " " + colnames[i]
+ "){\r\n");
sb.append("\t\tthis." + colnames[i] + " = " + colnames[i] + ";\r\n");
sb.append("\t}\r\n");
sb.append("\tpublic " + sqlType2JavaType(colTypes[i]) + " get"
+ initcap(colnames[i]) + "(){\r\n");
sb.append("\t\treturn " + colnames[i] + ";\r\n");
sb.append("\t}\r\n");
}
}
/**
* 解析输出属性
*
* @return
*/
private void processAllAttrs(StringBuffer sb) {
for (int i = 0; i < colnames.length; i++) {
sb.append("\tprivate " + sqlType2JavaType(colTypes[i]) + " " + colnames[i] + ";\r\n");
}
}
/**
* 解析生成构造函数
*
* @return
*/
private void processConstructor(StringBuffer sb,String tableName) {
sb.append("\tpublic "+tableName+"(){}\r\n");
sb.append("\tpublic "+tableName+"(");
String link="";
for (int i = 0; i < colnames.length; i++) {
sb.append(link + sqlType2JavaType(colTypes[i]) + " " + colnames[i] );
link=",";
}
sb.append("){\r\n");
for (int i = 0; i < colnames.length; i++) {
sb.append("\t\tthis."+colnames[i]+"="+colnames[i]+";\r\n");
}
sb.append("\t}\r\n");
}
/**
* 把输入字符串的首字母改成大写
*
* @param str
* @return
*/
private String initcap(String str) {
char[] ch = str.toCharArray();
if (ch[0] >= 'a' && ch[0] <= 'z') {
ch[0] = (char) (ch[0] - 32);
}
return new String(ch);
}
private String sqlType2JavaType(String sqlType) {
if (sqlType.equalsIgnoreCase("bit")) {
return "boolean";
} else if (sqlType.equalsIgnoreCase("tinyint")) {
return "byte";
} else if (sqlType.equalsIgnoreCase("smallint")) {
return "short";
} else if (sqlType.equalsIgnoreCase("int")) {
return "int";
} else if (sqlType.equalsIgnoreCase("bigint")) {
return "long";
} else if (sqlType.equalsIgnoreCase("float")) {
return "float";
} else if (sqlType.equalsIgnoreCase("decimal")
|| sqlType.equalsIgnoreCase("numeric")
|| sqlType.equalsIgnoreCase("real")) {
return "double";
} else if (sqlType.equalsIgnoreCase("money")
|| sqlType.equalsIgnoreCase("smallmoney")) {
return "double";
} else if (sqlType.equalsIgnoreCase("varchar")
|| sqlType.equalsIgnoreCase("char")
|| sqlType.equalsIgnoreCase("nvarchar")
|| sqlType.equalsIgnoreCase("nchar")
|| sqlType.equalsIgnoreCase("uniqueidentifier")
|| sqlType.equalsIgnoreCase("ntext")) {
return "String";
} else if (sqlType.equalsIgnoreCase("datetime")
||sqlType.equalsIgnoreCase("date")){
return "Date";
}
else if (sqlType.equalsIgnoreCase("image")) {
return "Blob";
} else if (sqlType.equalsIgnoreCase("text")) {
return "Clob";
}
return "String";
}
public static void main(String[] args) throws Exception {
AutoCreateBean auto = new AutoCreateBean();
List<String> list = auto.TBlist();
//注意，这点也特别需要注意，com/vo是包名
auto.GenEntity(list, "com/vo");
}
}提示：一些需要注意的地方已经特别提示了，只需要照做，因该就没问题了。
Q2：数据访问层（dao）
package com;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.*;
/**
* 根据数据库表结构 自动生成java MVC中的dao
*
* @author DLHT 2018年5月10日下午15:00:28 AutoCreateDao.java
*/
public class AutoCreateDao {
/*// sql 驱动类
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
// 数据库登录用户名
private static final String USER = "";
// 数据库登录密码
private static final String PASSWORD = "";
// 数据库连接地址
private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=TakeawayDB";*/
public static String DRIVER = "com.mysql.jdbc.Driver";
public static String URL = "jdbc:mysql://localhost:3306/schooldb?serverTimezone=GMT%2B8";
public static String USER = "root";
public static String PASSWORD = "";
private static String tablename;
private String[] colnames; // 列名数组
private String[] colTypes; // 列名类型数组
private int[] colSizes; // 列名大小数组
/**
* 获取指定数据库中包含的表 TBlist
*
* @time 2016年3月4日下午5:54:52
* @packageName com.util
* @return 返回所有表名(将表名放到一个集合中)
* @throws Exception
*/
public List<String> TBlist() throws Exception {
// 访问数据库 采用 JDBC方式
Class.forName(DRIVER);
Connection con = DriverManager.getConnection(URL, USER, PASSWORD);
DatabaseMetaData md = con.getMetaData();
List<String> list = null;
ResultSet rs = md.getTables(null, null, "teacher", null);
if (rs != null) {
list = new ArrayList<String>();
}
while (rs.next()) {
// System.out.println("|表" + (i++) + ":" + rs.getString("TABLE_NAME"));
String tableName = rs.getString("TABLE_NAME");
list.add(tableName);
}
rs = null;
md = null;
con = null;
return list;
}
public void GenEntity(List<String> TBlist, String packageName)throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSetMetaData rsmd = null;
// 访问数据库 采用 JDBC方式
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
for (int k = 0; k < TBlist.size(); k++) {
tablename = TBlist.get(k);
String strsql = "select * from " + tablename;
pstmt = conn.prepareStatement(strsql);
rsmd = pstmt.getMetaData();
int size = rsmd.getColumnCount();
// 共有多少列
colnames = new String[size];
colTypes = new String[size];
colSizes = new int[size];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
colnames[i] = rsmd.getColumnName(i + 1);
colTypes[i] = rsmd.getColumnTypeName(i + 1);
/*
if (colTypes[i].equalsIgnoreCase("datetime")) {
f_util = true;
}
if (colTypes[i].equalsIgnoreCase("image")
|| colTypes[i].equalsIgnoreCase("text")) {
f_sql = true;
}*/
colSizes[i] = rsmd.getColumnDisplaySize(i + 1);
}
markerBean(initcap(tablename), parse(), packageName);
}
pstmt = null;
rsmd = null;
conn = null;
}
/**
*
* 解析处理(生成实体类主体代码)
*/
private String parse() {
StringBuffer sb = new StringBuffer();
//导入包
sb.append("import java.sql.*;\r\n");
sb.append("import java.util.*;\r\n");
//自己新加一个导入包
sb.append("import util.DBUtil;\r\n");
//自己新加一个时间导入包
sb.append("import java.sql.Date;\r\n");
//导入对应实体包
sb.append("import com.vo." + initcap(tablename) + ";\r\n");
sb.append("public class " + initcap(tablename) + "DAO {\r\n");
processAllMethod(sb);
sb.append("}\r\n");
return sb.toString();
}
/**
* 创建java 文件 将生成的属性 get/set 方法 保存到 文件中 markerBean
*
* @time 2015年9月29日下午4:15:22
* @packageName fanshe
* @param className
* 类名称
* @param content
* 类内容 包括属性 getset 方法
*/
public void markerBean(String className, String content, String packageName) {
String folder = System.getProperty("user.dir") + "/src/" + packageName + "/";
File file = new File(folder);
if (!file.exists()) {
file.mkdirs();
}
String fileName = folder + className + "DAO.java";
try {
File newdao = new File(fileName);
FileWriter fw = new FileWriter(newdao);
fw.write("package\t" + packageName.replace("/", ".") + ";\r\n");
fw.write(content);
fw.flush();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 生成所有的方法
*
* @param sb
*/
private void processAllMethod(StringBuffer sb) {
/*生成五个方法：
* findById,findAll,insert,update,delete
*/
//getModel
sb.append("\tpublic List<" + initcap(tablename) + "> getModel("+sqlType2JavaType(colTypes[0])+" "+colnames[0]+") {\r\n");
sb.append("\t\tString sql = \"select * from " + tablename + " where "+colnames[0]+"=?\";\r\n");
sb.append("\t\tObject[] in = {"+colnames[0]+"};\r\n");
sb.append("\t\tResultSet rs = DBUtil.executeQuery(sql, in);\r\n");
sb.append("\t\tList<" + initcap(tablename) + "> list = new ArrayList<" + initcap(tablename) + ">();\r\n");
sb.append("\t\t" + initcap(tablename) + " model = null;\r\n");
sb.append("\t\ttry {\r\n");
sb.append("\t\t\tif(rs.next()) {\r\n");
sb.append("\t\t\t\tmodel = new " + initcap(tablename) + "(\r\n");
String link="";
for (int i = 0; i < colnames.length; i++) {
sb.append(link+"\t\t\t\t\trs.get" + sqlTypeJavaType(colTypes[i]) + "(\"" + colnames[i] + "\")");
link=",\r\n";
}
sb.append(");\r\n");
sb.append("\t\t\t\tlist.add(model);\r\n");
sb.append("\t\t\t}\r\n");
sb.append("\t\t\tDBUtil.close();\r\n");
sb.append("\t\t} catch (SQLException e) {\r\n");
sb.append("\t\t\te.printStackTrace();\r\n");
sb.append("\t\t}\r\n");
sb.append("\t\treturn list;\r\n");
sb.append("\t}\r\n");
//getModels
sb.append("\tpublic List<" + initcap(tablename) + "> getModels() {\r\n");
sb.append("\t\tString sql = \"select * from " + tablename + "\";\r\n");
sb.append("\t\tResultSet rs = DBUtil.executeQuery(sql);\r\n");
sb.append("\t\tList<" + initcap(tablename) + "> list = new ArrayList<" + initcap(tablename) + ">();\r\n");
sb.append("\t\t" + initcap(tablename) + " model = null;\r\n");
sb.append("\t\ttry {\r\n");
sb.append("\t\t\twhile(rs.next()) {\r\n");
sb.append("\t\t\t\tmodel = new " + initcap(tablename) + "(\r\n");
link="";
for (int i = 0; i < colnames.length; i++) {
sb.append(link+"\t\t\t\t\trs.get" + sqlTypeJavaType(colTypes[i]) + "(\"" + colnames[i] + "\")");
link=",\r\n";
}
sb.append(");\r\n");
sb.append("\t\t\t\tlist.add(model);\r\n");
sb.append("\t\t\t}\r\n");
sb.append("\t\t\tDBUtil.close();\r\n");
sb.append("\t\t} catch (SQLException e) {\r\n");
sb.append("\t\t\te.printStackTrace();\r\n");
sb.append("\t\t}\r\n");
sb.append("\t\treturn list;\r\n");
sb.append("\t}\r\n");
/*//insert
sb.append("\tpublic int insert(" + initcap(tablename) + " model) {\r\n");
//添加相同个数的？
String linkinsert="";
for (int i = 1; i < colnames.length; i++) {
linkinsert+="?";
if(i<colnames.length-1) {
linkinsert+=",";
}
}
sb.append("\t\tString sql = \"insert into " + tablename + " values("+linkinsert+") \";\r\n");
sb.append("\t\tObject[] in = {");
link="";
for (int i = 1; i < colnames.length; i++) {
sb.append(link+"model.get"+initcap(colnames[i])+"()");
link=",";
}
sb.append("};\r\n");
//DBUtil...改成自己项目的util
sb.append("\t\treturn DBUtil.executeUpdate(sql, in);\r\n");
sb.append("\t}\r\n"); */
//insert
String linkinserttop="";
for (int i = 1; i <colnames.length ; i++) {//调用类型，使其进行改变
linkinserttop+=sqlType2JavaType(colTypes[i])+" "+colnames[i];
if(i<colnames.length-1) {
linkinserttop+=",";
}
}
sb.append("\tpublic int insert(" + linkinserttop + ") {\r\n");
//添加相同个数的？
String linkinsert="";
for (int i = 1; i < colnames.length; i++) {
linkinsert+="?";
if(i<colnames.length-1) {
linkinsert+=",";
}
}
sb.append("\t\tString sql = \"insert into " + tablename + " values("+linkinsert+")\";\r\n");
sb.append("\t\tObject[] in = {");
link="";
for (int i = 1; i < colnames.length; i++) {
sb.append(link+colnames[i]);
link=",";
}
sb.append("};\r\n");
//DBUtil...改成自己项目的util
sb.append("\t\treturn DBUtil.executeUpdate(sql, in);\r\n");
sb.append("\t}\r\n");
//update
String linkupdate1="";
for (int i = colnames.length-1; i >=0 ; i--) {//调用类型，使其进行改变
linkupdate1+=sqlType2JavaType(colTypes[i])+" "+colnames[i];
if(i>0) {
linkupdate1+=",";
}
}
sb.append("\tpublic int update("+linkupdate1+") {\r\n");
String linkupdate2="set"+" ";
for (int i = 1; i < colnames.length; i++) {
linkupdate2+=colnames[i]+"=?";
if(i<colnames.length-1) {
linkupdate2+=",";
}
}
sb.append("\t\tString sql = \"update " + tablename + " "+linkupdate2+" where "+colnames[0]+"=?\";\r\n");
sb.append("\t\tObject[] in = {");
link="";
for (int i = colnames.length-1; i >=0 ; i--) {
sb.append(link+colnames[i]);
if(i>0) {
link=",";
}
}
sb.append("};\r\n");
//DBUtil...改成自己项目的util
sb.append("\t\treturn DBUtil.executeUpdate(sql, in);\r\n");
sb.append("\t}\r\n");
//delete
sb.append("\tpublic int delete("+sqlType2JavaType(colTypes[0])+" "+colnames[0]+") {\r\n");
sb.append("\t\tString sql = \"delete from " + tablename + " where "+colnames[0]+"=?\";\r\n");
sb.append("\t\tObject[] in = {"+colnames[0]+"};\r\n");
//DBUtil...改成自己项目的util
sb.append("\t\treturn DBUtil.executeUpdate(sql, in);\r\n");
sb.append("\t}\r\n");
}
/**
* 把输入字符串的首字母改成大写
*
* @param str
* @return
*/
private String initcap(String str) {
char[] ch = str.toCharArray();
if (ch[0] >= 'a' && ch[0] <= 'z') {
ch[0] = (char) (ch[0] - 32);
}
return new String(ch);
}
private String sqlType2JavaType(String sqlType) {
if (sqlType.equalsIgnoreCase("bit")) {
return "boolean";
} else if (sqlType.equalsIgnoreCase("tinyint")) {
return "byte";
} else if (sqlType.equalsIgnoreCase("smallint")) {
return "short";
} else if (sqlType.equalsIgnoreCase("int")) {
return "int";
} else if (sqlType.equalsIgnoreCase("bigint")) {
return "long";
} else if (sqlType.equalsIgnoreCase("float")) {
return "float";
} else if (sqlType.equalsIgnoreCase("decimal")
|| sqlType.equalsIgnoreCase("numeric")
|| sqlType.equalsIgnoreCase("real")) {
return "double";
} else if (sqlType.equalsIgnoreCase("money")
|| sqlType.equalsIgnoreCase("smallmoney")) {
return "double";
} else if (sqlType.equalsIgnoreCase("varchar")
|| sqlType.equalsIgnoreCase("char")
|| sqlType.equalsIgnoreCase("nvarchar")
|| sqlType.equalsIgnoreCase("nchar")
|| sqlType.equalsIgnoreCase("uniqueidentifier")
|| sqlType.equalsIgnoreCase("ntext")) {
return "String";
} else if (sqlType.equalsIgnoreCase("datetime")
||sqlType.equalsIgnoreCase("date")){
return "Date";
}
else if (sqlType.equalsIgnoreCase("image")) {
return "Blob";
} else if (sqlType.equalsIgnoreCase("text")) {
return "Clob";
}
return "String";
}
private String sqlTypeJavaType(String sqlType) {
if (sqlType.equalsIgnoreCase("bit")) {
return "Boolean";
} else if (sqlType.equalsIgnoreCase("tinyint")) {
return "Byte";
} else if (sqlType.equalsIgnoreCase("smallint")) {
return "Short";
} else if (sqlType.equalsIgnoreCase("int")) {
return "Int";
} else if (sqlType.equalsIgnoreCase("bigint")) {
return "Long";
} else if (sqlType.equalsIgnoreCase("float")) {
return "Float";
} else if (sqlType.equalsIgnoreCase("decimal")
|| sqlType.equalsIgnoreCase("numeric")
|| sqlType.equalsIgnoreCase("real")) {
return "Double";
} else if (sqlType.equalsIgnoreCase("money")
|| sqlType.equalsIgnoreCase("smallmoney")) {
return "Double";
} else if (sqlType.equalsIgnoreCase("varchar")
|| sqlType.equalsIgnoreCase("char")
|| sqlType.equalsIgnoreCase("nvarchar")
|| sqlType.equalsIgnoreCase("nchar")
|| sqlType.equalsIgnoreCase("uniqueidentifier")
|| sqlType.equalsIgnoreCase("ntext")) {
return "String";
} else if (sqlType.equalsIgnoreCase("datetime")
||sqlType.equalsIgnoreCase("date")){
return "Date";
}
else if (sqlType.equalsIgnoreCase("image")) {
return "Blob";
} else if (sqlType.equalsIgnoreCase("text")) {
return "Clob";
}
return "String";
}
public static void main(String[] args) throws Exception {
AutoCreateDao auto = new AutoCreateDao();
List<String> list = auto.TBlist();
auto.GenEntity(list, "com/dao");
}
}提示：一些需要注意的地方已经特别提示了，只需要照做，因该就没问题了。
Q3:DBUTIL
package util;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class DBUtil {
//连接对象
//Statement 命令对象
//打开连接
//关闭连接
//得到一个连接对象
//查询（有参，无参）
//修改（有参，无参）
static Connection conn = null;
static Statement stmt = null;
//驱动，服务器地址，登录用户名，密码
static String DBDRIVER;
static String DBURL;
static String DBUSER;
static String DBPWD;
static {
//先创建资源文件，扩展名为.properties
//内容是以：dbuser=sa 格式
Properties prop = new Properties();//先获取资源对象
try {
prop.load(Thread.currentThread().getContextClassLoader().
getResourceAsStream("/resources/dbconfig.properties"));
DBDRIVER = prop.getProperty("DBDRIVER");
DBURL = prop.getProperty("DBURL");
DBUSER = prop.getProperty("DBUSER");
DBPWD = prop.getProperty("DBPWD");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//打开连接
public static void open() {
//加载驱动
try {
Class.forName(DBDRIVER);
conn=DriverManager.getConnection(DBURL,DBUSER,DBPWD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭连接
public static void close() {
try {
if(stmt!=null && stmt.isClosed())
stmt.close();
if(conn!=null && !conn.isClosed())
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//得到一个连接对象，当用户使用DBUtil无法解决个性问题时
//可以通过本方法获得连接对象
public static Connection getConnection() {
try {
if(conn==null ||conn.isClosed())
open();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//executeQuery
//executeUpdate
//execute
//获得查询的数据集
//不带参数的查询
//select * from student where name='' and sex=''
public static ResultSet executeQuery(String sql) {
try {
open();//保证连接是成功的
stmt = conn.createStatement();
return stmt.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//修改表格内容
public static int executeUpdate(String sql) {
int result = 0;
try {
open();//保证连接是成功的
stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
close();
}
return result;
}
//如果执行的查询或存储过程，会返回多个数据集，或多个执行成功记录数
//可以调用本方法，返回的结果，
//是一个List<ResultSet>或List<Integer>集合
public static Object execute(String sql) {
boolean b=false;
try {
open();//保证连接是成功的
stmt = conn.createStatement();
b = stmt.execute(sql);
//true,执行的是一个查询语句，我们可以得到一个数据集
//false,执行的是一个修改语句，我们可以得到一个执行成功的记录数
if(b){
return stmt.getResultSet();
}
else {
return stmt.getUpdateCount();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(!b) {
close();
}
}
return null;
}
//
//select * from student where name=? and sex=?
//带参数的查询，只有输入参数
public static ResultSet executeQuery(String sql,Object[] in) {
try {
open();//保证连接是成功的
PreparedStatement pst = conn.prepareStatement(sql);
for(int i=0;i<in.length;i++)
pst.setObject(i+1, in[i]);
stmt = pst;//只是为了关闭命令对象pst
return pst.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//带参数修改,只有输入参数
public static int executeUpdate(String sql,Object[] in) {
try {
open();//保证连接是成功的
PreparedStatement pst = conn.prepareStatement(sql);
for(int i=0;i<in.length;i++)
pst.setObject(i+1, in[i]);
stmt = pst;//只是为了关闭命令对象pst
return pst.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
//e.printStackTrace();
}finally {
close();
}
return 0;
}
public static Object execute(String sql,Object[] in) {
boolean b=false;
try {
open();//保证连接是成功的
PreparedStatement pst = conn.prepareStatement(sql);
for(int i=0;i<in.length;i++)
pst.setObject(i+1, in[i]);
b = pst.execute();
//true,执行的是一个查询语句，我们可以得到一个数据集
//false,执行的是一个修改语句，我们可以得到一个执行成功的记录数
if(b){
System.out.println("----");
/*List<ResultSet> list = new ArrayList<ResultSet>();
list.add(pst.getResultSet());
while(pst.getMoreResults()) {
list.add(pst.getResultSet());
}*/
return pst.getResultSet();
}
else {
System.out.println("****");
List<Integer> list = new ArrayList<Integer>();
list.add(pst.getUpdateCount());
while(pst.getMoreResults()) {
list.add(pst.getUpdateCount());
}
return list;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(!b) {
System.out.println("====");
close();
}
}
return null;
}
//调用存储过程 proc_Insert(?,?,?)
public static Object executeProcedure(String procName,Object[] in) {
open();
try {
procName = "{call "+procName+"(";
String link="";
for(int i=0;i<in.length;i++) {
procName+=link+"?";
link=",";
}
procName+=")}";
CallableStatement cstmt = conn.prepareCall(procName);
for(int i=0;i<in.length;i++) {
cstmt.setObject(i+1, in[i]);
}
if(cstmt.execute())
{
return cstmt.getResultSet();
}
else {
return cstmt.getUpdateCount();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/*
* 调用存储过程，并有输出参数
* @procName ，存储过程名称：proc_Insert(?,?)
* @in ,输入参数集合
* @output,输出参数集合
* @type,输出参数类型集合
* */
public static Object executeOutputProcedure(String procName,
Object[] in,Object[] output,int[] type){
Object result = null;
try {
CallableStatement cstmt = conn.prepareCall("{call "+procName+"}");
//设置存储过程的参数值
int i=0;
for(;i<in.length;i++){//设置输入参数
cstmt.setObject(i+1, in[i]);
//print(i+1);
}
int len = output.length+i;
for(;i<len;i++){//设置输出参数
cstmt.registerOutParameter(i+1,type[i-in.length]);
//print(i+1);
}
boolean b = cstmt.execute();
//获取输出参数的值
for(i=in.length;i<output.length+in.length;i++)
output[i-in.length] = cstmt.getObject(i+1);
if(b) {
result = cstmt.getResultSet();
}
else {
result = cstmt.getUpdateCount();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
//调用存储过程查询出所有的值
public static Object executeProcedures(String procName) {
open();
try {
procName = "{call "+procName+"}";
CallableStatement cstmt = conn.prepareCall(procName);
if(cstmt.execute())
{
return cstmt.getResultSet();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}Q4:mysql配置文件
DBDRIVER=com.mysql.jdbc.Driver
DBURL=jdbc:mysql://localhost:3306/schooldb?serverTimezone=GMT%2B8
DBUSER=root
DBPWD=123456Q5:sqlserver配置文件
DBDRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver
DBURL=jdbc:sqlserver://localhost:1433;DatabaseName=\u7528\u6237\u7BA1\u7406s
DBUSER=sa
DBPWD=123456注意:DBUTIL与DBUtil连着用
//以下为图片提示
zywds
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
