Linux上でJavaからMySQL接続

Linux上でJavaからMySQL接続を実施

1. connectorのダウンロード
まずhttp://dev.mysql.com/downloads/connector/よりConnector/J
をダウンロード、展開する。
その中のmysql-connector-java-5.1.37-bin.jarをサーバにFTP等でアップロード。

2. クラスパスの設定
$ vi .bashrc
export CLASSPATH=/usr/local/lib/mysql-connector-java-5.1.37-bin.jar:/usr/local/lib/lpsolve55j.jar:$CLASSPATH
前回の設定分に赤の部分を追加しておく
$ source .bashrc

3. MySQL接続クラスをつける
class MySQL {

        // JDBCドライバの登録
        String driver;
        // データベースの指定
        String server, dbname, url, user, password;
        Connection con;
        Statement stmt;
        ResultSet rs;

        public MySQL() {
                this.driver = "org.gjt.mm.mysql.Driver";
                this.server = "localhost";
                this.dbname = "naisyo";
                this.url = "jdbc:mysql://" + server + "/" + dbname + "?useUnicode=true&characterEncoding=UTF-8";                      this.user = "naisyo";
                this.password = "naisyo";
                try {
                        this.con = DriverManager.getConnection(url, user, password);
                        this.stmt = con.createStatement ();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block                                                     
                        e.printStackTrace();
                }                                                                                                     
                try {
                        Class.forName (driver);                                                                       
                } catch (ClassNotFoundException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }                                                                                                             
        public void close(){
                try {
                        rs.close();
                        stmt.close();
                        con.close();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }

        public ResultSet selectAll(){

                String sql = "SELECT * FROM naisyo";
                ResultSet rs = null;
                try {
                        rs = stmt.executeQuery (sql);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }  //try catchで囲む
                return rs;
        }

}

main関数から呼び出し
       MySQL mysql = new MySQL();
        ResultSet rs = mysql.selectAll();

        try {
                while(rs.next()){
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        System.out.println("ID:" + id);
                        System.out.println("Name:" + name);
                }
        } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
これで表示されれば、データベースとの接続＆情報取り出しOK