java与mysql的简单连接与编译

来源：互联网发布：saber软件正版价格编辑：程序博客网时间：2024/05/17 20:33

先下载Mysql的驱动类库

http://dev.mysql.com/downloads/connector/j/

解压主要用到mysql-connector-java-5.1.40.jar

下面给mysql-connector-java-5.1.40.jar起一个简短的名字mysqldriver.jar

一个简短的代码：

import java.sql.*;

public class DBTester {
    public static void main(String[] args) throws Exception {
        Connection con;
        Statement stmt;
        ResultSet rs;
        // 加载驱动器
        Class.forName("com.mysql.jdbc.Driver");
        // 注册mysql驱动器
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        // 连接数据库
        String dbUrl = "jdbc:mysql://localhost:3306/M_STOREDB";
        String dbUser = "bozi";
        String dbPwd = "1234";
        // 建立数据库的连接
        con = java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);
        // 创建一个Statement对象
        stmt = con.createStatement();

        String name1 = new String("小王");
        String address1 = new String("上海");

        // 增加新纪录
        stmt.executeUpdate("insert into M_CUSTOMERS(NAME, AGE, ADDRESS)"
                + "VALUE('" + name1+"',20,'" + address1 + "')");

        // 查询记录
        rs = stmt.executeQuery("select ID, NAME, AGE , ADDRESS from M_CUSTOMERS");

        // 输出查询结果
        while (rs.next()) {
            long id = rs.getLong(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String address = rs.getString(4);

            // 打印数据
            System.out.println("id=" + id +", name=" + name +",age=" + age+",address="
                    + address);
        }

        // 删除增加的记录
        stmt.executeUpdate("delete from M_CUSTOMERS where name ='"
                + name1 +"'");

        // 释放相关的资源
        rs.close();
        stmt.close();
        con.close();
    }
}

编译【将mysqldriver.jar 放在和源码同样的位置方便下面】

javac -cp ./mysqldriver.jar DBTester.java

运行

java -cp .:./mysqldriver.jar DBTester

id=1, name=小红,age=23,address=北京

id=2, name=小张,age=29,address=天津

id=3, name=小丁,age=33,address=山东

id=8, name=小王,age=20,address=上海

直接javac会出错

javac DBTester.java

DBTester.java:17: 错误: 程序包com.mysql.jdbc不存在

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

1 个错误

相关代码 实现从外部文件读取配置

所有文件

-rw-rw-r-- 1 alick alick 1030 12月 6 12:31 ConnectionProvider.java

-rw-rw-r-- 1 alick alick 121 12月 6 12:44 db.conf

-rw-rw-r-- 1 alick alick 5115 12月 6 16:10 DBTester2.java

-rw-rw-r-- 1 alick alick 2720 12月 6 17:01 DBTester3.java

-rw-rw-r-- 1 alick alick 782 12月 6 12:32 PropertyReader.java

配置文件 db.conf

alick@alick-Ubuntu:~/workspace/sql_text/test_1$ cat db.conf

JDBC_DRIVER=com.mysql.jdbc.Driver
DB_URL=jdbc:mysql://localhost:3306/M_STOREDB?useSSL=true-------------------【mysql server 5.7.16-0 不加这个出现警告，必须显示的加】
DB_USER=bozi
DB_PASSWORD=1234

读取配置的类

alick@alick-Ubuntu:~/workspace/sql_text/test_1$ cat PropertyReader.java

import java.util.*;
import java.io.*;

public class PropertyReader {
    static private Properties ps;
    static {
        ps = new Properties();
        try {
            // 假定db.conf文件与PropertyReader.class文件在同一个目录下
            InputStream in = PropertyReader.class.getResourceAsStream("./db.conf");
            ps.load(in);
            in.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public static String get(String key) {
    //    return ps.getProperty(key);
        return (String)ps.get(key);
    }
}

实现连接的类

alick@alick-Ubuntu:~/workspace/sql_text/test_1$ cat ConnectionProvider.java

import java.sql.*;

public class ConnectionProvider {
    private String JDBC_DRIVER;
    private String DB_URL;
    private String DB_USER;
    private String DB_PASSWORD;

    public ConnectionProvider() {
        JDBC_DRIVER = PropertyReader.get("JDBC_DRIVER");
        DB_URL = PropertyReader.get("DB_URL");
        DB_USER = PropertyReader.get("DB_USER");
        DB_PASSWORD = PropertyReader.get("DB_PASSWORD");

        try {
            Class jdbcDriver = Class.forName(JDBC_DRIVER);
            java.sql.DriverManager.registerDriver((Driver)jdbcDriver.newInstance());
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {
        Connection con = java.sql.DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
        return con;
    }
}

线程安全的操作

alick@alick-Ubuntu:~/workspace/sql_text/test_1$ cat DBTester2.java

/* 通过ConnectionProvider类来获取Connection对象 */

import java.sql.*;
public class DBTester2 {
    private ConnectionProvider provider;
    public DBTester2(ConnectionProvider provider) {
        this.provider = provider;
    }

    public void addCustomer(String name, int age, String address) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        try {
            con = provider.getConnection();
            stmt = con.createStatement();
            String sql = "insert into M_CUSTOMERS(NAME, AGE, ADDRESS) values ('" +name+"'," + age + ",'" + address+"')";
            stmt.execute(sql);
        } finally {
            closeStatement(stmt);
            closeConnection(con);
        }
    }

    public void deleteCustomer(String name) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        try {
            con = provider.getConnection();
            stmt = con.createStatement();
            String sql = "delete from M_CUSTOMERS where NAME = '" + name +"'";
            stmt.execute(sql);
        } finally {
            closeStatement(stmt);
            closeConnection(con);
        }
    }

    public void printAllCumosters() throws SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = provider.getConnection();
            stmt = con.createStatement();
            // 查询记录
            rs = stmt.executeQuery("select ID, NAME, AGE, ADDRESS from M_CUSTOMERS");
            // 输出查询结果
            while (rs.next()) {
                long id = rs.getLong(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                String address = rs.getString(4);
                System.out.println("id=" + id + ",name=" + name + ", age=" + age + ", address=" + address);

            }
        } finally {
                closeResultSet(rs);
                closeStatement(stmt);
                closeConnection(con);
          }

    }

    // 展示订单用户
    public void showOrdersOwner() {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = provider.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery("select NAME from M_CUSTOMERS where ID = 1");
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
            // 注意： 上一个打开的ResultSet自动关闭
            rs = stmt.executeQuery("select NAME from M_CUSTOMERS where ID = 2");
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(con);
        }
    }

    // 展示每个客户的所有的订单
    public void showOrders() throws SQLException {
    //    Connection con = null;
    //    Statement stmt1 = null;
    //    Statement stmt2 = null;
    //    ResultSet rs1 = null;
    //    ResultSet rs2 = null;
    //    try {
    //        con = provider.getConnection();
    //        stmt1 = con.createStatement();
    //        stmt2 = con.createStatement();

    //        rs1 = stmt1.executeQuery("select ID, NAME from M_CUSTOMERS");
    //        while (rs1.next()) {
    //            // 执行第二个循环出错
    //            long id = rs1.getLong(1);
    //            String name = rs1.getString(2);

    //            rs2 = stmt2.executeQuery("select ORDER_NUMBER, PRICE from M_ORDERS where CUSTOMER_ID=" + id);
    //            while (rs2.next()) {
    //                String orderNumber = rs2.getString(1);
    //                float price = rs2.getFloat(2);
    //                System.out.println("name=" + name + ", orderNumber= " + orderNumber + ", price=" + price);
    //             }
    //            rs2.close();
    //        }
    //        rs1.close();

    //    } finally {
    //    //    closeResultSet(rs);
    //        closeStatement(stmt1);
    //        closeStatement(stmt2);
    //        closeConnection(con);
    //    }
    Connection con = null;
    Statement stmt = null;
try{

    // 更高效的做法
    con = provider.getConnection();
    stmt = con.createStatement();
    // 使用右外连接查询语句
    ResultSet rs = stmt.executeQuery("select NAME, ORDER_NUMBER, PRICE from M_CUSTOMERS c right join M_ORDERS o on c.ID = o.CUSTOMER_ID");
    while (rs.next()) {
        String name = rs.getString(1);
        String orderNumber = rs.getString(2);
        float price = rs.getFloat(3);
        System.out.println("name="+ name +",orderNumber="+orderNumber+",price="+ price);
    }
        rs.close();
} finally {
        stmt.close();
        con.close();
    }
}

    private void closeResultSet(ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void closeStatement(Statement stmt) {
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void closeConnection(Connection con) {
        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        DBTester2 tester = new DBTester2(new ConnectionProvider());
    //    tester.addCustomer("小王", 20, "上海");
    //    tester.printAllCumosters();
    //    tester.deleteCustomer("小王");
    //    tester.showOrdersOwner();
        tester.showOrders();

    }
}

线程不安全但是效率高的操作

alick@alick-Ubuntu:~/workspace/sql_text/test_1$ cat DBTester3.java

/* DBTester3 和Connection对象有相同的生命周期   */
import java.sql.*;

class DBTester3 {
    private ConnectionProvider provider;
    private Connection con;
    private PreparedStatement addStmt; // 用于addCustomers
    private PreparedStatement deleteStmt; // 用于deleteCustomers
    private PreparedStatement findStmt; // 用于 findCustomers

    public DBTester3(ConnectionProvider provider) throws SQLException {
        this.provider = provider;
        con = provider.getConnection();
    }

    public void addCustomers(String name, int age, String address) throws SQLException {
        String sql = "insert into M_CUSTOMERS(NAME, AGE, ADDRESS) values (?,?,?)";
        if (addStmt == null) {
            addStmt = con.prepareStatement(sql);
        }
        addStmt.setString(1, name);
        addStmt.setInt(2, age);
        addStmt.setString(3, address);
        addStmt.execute();
    }

    public void deleteCustomer(String name) throws SQLException {
        String sql = "delete from M_CUSTOMERS where NAME=?";
        if (deleteStmt == null) {
            deleteStmt = con.prepareStatement(sql);
        }
        deleteStmt.setString(1, name);
        deleteStmt.execute();
    }

    public void findCustomer(String name, int age) throws SQLException {
        String sql = "select ID, NAME, AGE, ADDRESS from M_CUSTOMERS where NAME=? and AGE= ?";
        if (findStmt == null) {
            findStmt = con.prepareStatement(sql);
        }
        findStmt.setString(1, name);
        findStmt.setInt(2, age);
        // 查询记录[不是线程安全的 同一个findStmt可能打开多个ResultSet 出错]
        ResultSet rs= findStmt.executeQuery();
        try {
            // 输出
            while (rs.next()) {
                long id = rs.getLong(1);
                String n = rs.getString(2);
                int a = rs.getInt(3);
                String address = rs.getString(4);
                System.out.println("id:"+id +",name:" +n +",age:"+age + ",address:" + a);
            }
        } finally {
            try {
                rs.close();
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void close() {
        try{
            addStmt.close();
            deleteStmt.close();
            findStmt.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        DBTester3 tester = new DBTester3(new ConnectionProvider());
        tester.addCustomers("小王",20,"上海");
        tester.addCustomers("小玲",30,"上海");
        tester.findCustomer("小王",20);
        tester.findCustomer("小玲",30);
        tester.deleteCustomer("小王");
        tester.deleteCustomer("小玲");

        tester.close();
    }
}

编译

javac -cp .:../mysqldriver.jar DBTester3.java

运行

java -cp .:../mysqldriver.jar DBTester3

. 当前目录 : 分隔 ../mysqldriver.jar 驱动器所在的相对位置

0 0