Rhythmk 一步一步学 JAVA（2） ： 操作 MYSQL 数据库

1.  下载  如：mysql-connector-java-5.1.22.zip  解压获取 jar 连接包。

2.   引入项目：     右键项目名--->Build Path—>Configure Build Path...  选择  Libraries 然后再选择 右边

                        第二个选项Add External Jars 导入 mysql -.jar

3.   新建数据表：

   

 CREATE TABLE `user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(32) NOT NULL,
  `Age` int(11) NOT NULL,
  `AddTime` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

 

4. 测试程序：

     

package App1.Rhythmk.com;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import com.mysql.jdbc.Statement;

/*
 * 
 *  建表脚本
 CREATE TABLE `user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(32) NOT NULL,
  `Age` int(11) NOT NULL,
  `AddTime` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 */


public class ThirdMysql {

    // final 类似 .NET 中 const
    private static final String DatabaseConURL = "jdbc:mysql://127.0.0.1:3306/rhythmkdb";

    /**
     *  rhythmK
     * 1.右键项目名--->Build Path—>Configure Build Path... 会弹出来一个框 在那四个选项选择
     * Libraries 然后再选择 右边 第二个选项Add External Jars
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Test();
        ExcuteByParam();
        Add();
        Update();
        Select();
        Del();
    }

    public static void Test() {
        try {
            Connection con = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager
                    .getConnection(DatabaseConURL, "root", "wangkun");
            Msg("Mysql 连接成功。");

        } catch (Exception ex) {
            Msg(ex.toString());
        }
    }

    public static void Add() {
        String sql = "Insert  into  `user` (Name ,Age, Addtime ) ";
        sql += " values ('rhythmk',21,'2012-1-1')  ";
        try {
            Connection con = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager
                    .getConnection(DatabaseConURL, "root", "wangkun");

            java.sql.Statement statement = con.createStatement();
            statement.executeUpdate(sql);
            ResultSet result = statement
                    .executeQuery("select  LAST_INSERT_ID() ");
            int resultID = 0;
            if (result.next()) {
                // 此出索引不是从0开始 ..
                resultID = result.getInt(1);
            }
            Msg("Add 数据库得到返回ID" + resultID);

        } catch (Exception ex) {
            Msg(ex.toString());
        }
    }

    public static void Update() {
        String sql = " Update `user` set Name= 'rhythmk.com' where ID<3 ";
        try {
            Connection con = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager
                    .getConnection(DatabaseConURL, "root", "wangkun");

            java.sql.Statement statement = con.createStatement();
            int resultID = statement.executeUpdate(sql);

            Msg("Update 数据库得到返回ID" + resultID);

        } catch (Exception ex) {
            Msg(ex.toString());
        }
    }

    public static void Del() {
        String sql = " Delete from `user`  where ID=3 ";
        try {
            Connection con = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager
                    .getConnection(DatabaseConURL, "root", "wangkun");

            java.sql.Statement statement = con.createStatement();
            int resultID = statement.executeUpdate(sql);

            Msg("Del 数据库得到返回ID" + resultID);

        } catch (Exception ex) {
            Msg(ex.toString());
        }
    }

    public static void Select() {
        String sql = " Select ID,Name from `user`   ";
        try {
            Connection con = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager
                    .getConnection(DatabaseConURL, "root", "wangkun");

            java.sql.Statement statement = con.createStatement();
            ResultSet result = statement.executeQuery(sql);

            String resultStr = "";
            while (result.next()) {
                resultStr += "\r\n ID=" + result.getString(1);
                resultStr += ", Name=" + result.getString("Name");
            }

            Msg(resultStr);
            // statement.executeUpdate(sql);

        } catch (Exception ex) {
            Msg(ex.toString());
        }
    }

    public static void ExcuteByParam() {
        String sql = "select * from  `user` where Name =?";
        try {
            Connection con = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager
                    .getConnection(DatabaseConURL, "root", "wangkun");

            java.sql.PreparedStatement preparedsm = con.prepareStatement(sql);
            preparedsm.setString(1, "rhythmk");
            ResultSet result = preparedsm.executeQuery();

            String resultStr = "";
            while (result.next()) {
                resultStr += "\r\n ID=" + result.getString(1);
                resultStr += ", Name=" + result.getString("Name");
            }

            Msg("ExcuteByParam:\r\n" + resultStr);
            // statement.executeUpdate(sql);

        } catch (Exception ex) {
            Msg(ex.toString());
        }
    }

    public static void Msg(String msg) {
        System.out.println(msg);
    }

}