JAVA操作MySQL数据库

下面所有代码来自jeecms源代码中

• 获取连接

public static Connection getConn(String dbHost, String dbPort,
            String dbName, String dbUser, String dbPassword) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String connStr = "jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName
                + "?user=" + dbUser + "&password=" + dbPassword
                + "&characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(connStr);
        return conn;
    }

• 创建数据库

public static void createDb(String dbHost, String dbPort, String dbName,
            String dbUser, String dbPassword) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String connStr = "jdbc:mysql://" + dbHost + ":" + dbPort + "?user="
                + dbUser + "&password=" + dbPassword
                + "&characterEncoding=UTF8";
        Connection conn = DriverManager.getConnection(connStr);
        Statement stat = conn.createStatement();
        String sql = "drop database if exists " + dbName;
        stat.execute(sql);
        sql = "create database " + dbName + " CHARACTER SET UTF8";
        stat.execute(sql);
        stat.close();
        conn.close();
    }

• 修改数据库编码格式

public static void changeDbCharset(String dbHost, String dbPort,
            String dbName, String dbUser, String dbPassword) throws Exception {
        Connection conn = getConn(dbHost, dbPort, dbName, dbUser, dbPassword);
        Statement stat = conn.createStatement();
        String sql = "ALTER DATABASE " + dbName + " CHARACTER SET UTF8";
        stat.execute(sql);
        stat.close();
        conn.close();
    }

• 创建表

public static void createTable(String dbHost, String dbPort, String dbName,
            String dbUser, String dbPassword, List<String> sqlList)
            throws Exception {
        Connection conn = getConn(dbHost, dbPort, dbName, dbUser, dbPassword);
        Statement stat = conn.createStatement();
        for (String dllsql : sqlList) {
            System.out.println(dllsql);
            stat.execute(dllsql);
        }
        stat.close();
        conn.close();
    }

• 读取SQL语句

/**
     * 读取sql语句。“/*”开头为注释，“;”为sql结束。
     * 
     * @param fileName
     *            sql文件地址
     * @return list of sql
     * @throws Exception
     */
    public static List<String> readSql(String fileName) throws Exception {
        BufferedReader br = new BufferedReader(new InputStreamReader(
                new FileInputStream(fileName), UTF8));
        List<String> sqlList = new ArrayList<String>();
        StringBuilder sqlSb = new StringBuilder();
        String s = null;
        while ((s = br.readLine()) != null) {
            if (s.startsWith("/*") || s.startsWith("#")
                    || StringUtils.isBlank(s)) {
                continue;
            }
            if (s.endsWith(";")) {
                sqlSb.append(s);
                sqlSb.setLength(sqlSb.length() - 1);
                sqlList.add(sqlSb.toString());
                sqlSb.setLength(0);
            } else {
                sqlSb.append(s);
            }
        }
        br.close();
        return sqlList;
    }