Java中自动生成实体类
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=123456
Q5:sqlserver配置文件
DBDRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver DBURL=jdbc:sqlserver://localhost:1433;DatabaseName=\u7528\u6237\u7BA1\u7406s DBUSER=sa DBPWD=123456
注意:DBUTIL与DBUtil连着用
//以下为图片提示
zywds
