下面所有代码来自jeecms源代码中
- 获取连接
1 public static Connection getConn(String dbHost, String dbPort, 2 String dbName, String dbUser, String dbPassword) throws Exception { 3 Class.forName("com.mysql.jdbc.Driver"); 4 Class.forName("com.mysql.jdbc.Driver").newInstance(); 5 String connStr = "jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName 6 + "?user=" + dbUser + "&password=" + dbPassword 7 + "&characterEncoding=utf8"; 8 Connection conn = DriverManager.getConnection(connStr); 9 return conn; 10 }
- 创建数据库
1 public static void createDb(String dbHost, String dbPort, String dbName, 2 String dbUser, String dbPassword) throws Exception { 3 Class.forName("com.mysql.jdbc.Driver"); 4 Class.forName("com.mysql.jdbc.Driver").newInstance(); 5 String connStr = "jdbc:mysql://" + dbHost + ":" + dbPort + "?user=" 6 + dbUser + "&password=" + dbPassword 7 + "&characterEncoding=UTF8"; 8 Connection conn = DriverManager.getConnection(connStr); 9 Statement stat = conn.createStatement(); 10 String sql = "drop database if exists " + dbName; 11 stat.execute(sql); 12 sql = "create database " + dbName + " CHARACTER SET UTF8"; 13 stat.execute(sql); 14 stat.close(); 15 conn.close(); 16 }
- 修改数据库编码格式
1 public static void changeDbCharset(String dbHost, String dbPort, 2 String dbName, String dbUser, String dbPassword) throws Exception { 3 Connection conn = getConn(dbHost, dbPort, dbName, dbUser, dbPassword); 4 Statement stat = conn.createStatement(); 5 String sql = "ALTER DATABASE " + dbName + " CHARACTER SET UTF8"; 6 stat.execute(sql); 7 stat.close(); 8 conn.close(); 9 }
- 创建表
1 public static void createTable(String dbHost, String dbPort, String dbName, 2 String dbUser, String dbPassword, List<String> sqlList) 3 throws Exception { 4 Connection conn = getConn(dbHost, dbPort, dbName, dbUser, dbPassword); 5 Statement stat = conn.createStatement(); 6 for (String dllsql : sqlList) { 7 System.out.println(dllsql); 8 stat.execute(dllsql); 9 } 10 stat.close(); 11 conn.close(); 12 }
- 读取SQL语句
1 /** 2 * 读取sql语句。“/*”开头为注释，“;”为sql结束。 3 * 4 * @param fileName 5 * sql文件地址 6 * @return list of sql 7 * @throws Exception 8 */ 9 public static List<String> readSql(String fileName) throws Exception { 10 BufferedReader br = new BufferedReader(new InputStreamReader( 11 new FileInputStream(fileName), UTF8)); 12 List<String> sqlList = new ArrayList<String>(); 13 StringBuilder sqlSb = new StringBuilder(); 14 String s = null; 15 while ((s = br.readLine()) != null) { 16 if (s.startsWith("/*") || s.startsWith("#") 17 || StringUtils.isBlank(s)) { 18 continue; 19 } 20 if (s.endsWith(";")) { 21 sqlSb.append(s); 22 sqlSb.setLength(sqlSb.length() - 1); 23 sqlList.add(sqlSb.toString()); 24 sqlSb.setLength(0); 25 } else { 26 sqlSb.append(s); 27 } 28 } 29 br.close(); 30 return sqlList; 31 }
