Java JDBC 连接Mysql数据库

以下Code在同一个Package中。亲测可以进行CURD操作。

JDBC的JdbcUtils类，例如：

package JDBC_Java_Mysql;

import java.sql.*;

/**
 * Created by dhc on 17-5-2. 
 * @author: 网络黑寡妇
 */
public class JdbcUtils{
    //获取数据库连接驱动
    private static String driver = "com.mysql.jdbc.Driver";   
    //获取数据库连接用户名
    private static String username = "root";
    //获取数据库连接密码
    private static String password = "123456";
    //获取数据库连接URL地址
    private static String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";

    static {
        try {
            //加载数据库驱动
            Class.forName(driver);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * @return Connection数据库连接对象
     * @Description: 获取数据库连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * @Description: 释放资源，
     * 要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象
     */
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                //关闭存储查询结果的ResultSet对象
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                //关闭负责执行SQL命令的Statement对象
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                //关闭Connection数据库连接对象
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

---

代码块

JdbcCRUDByStatement类利用Statement对象操作数据库（建议使用PreparedStatement对象操作），例如：

package JDBC_Java_Mysql;

import java.sql.*;

/**
 * Created by dhc on 17-5-2. 
 * @ClassName: JdbcCRUDByStatement
 * @Description: 通过Statement对象完成对数据库的CRUD操作
 * @author: 网络黑寡妇
 */
public class JdbcCRUDByStatement{
    private static Connection conn = null;
    private static Statement st = null;
    private static ResultSet rs = null;

    public void insert(String strSql) {
        try {
            //获取一个数据库连接
            conn = JdbcUtils.getConnection();
            //通过conn对象获取负责执行SQL命令的Statement对象
            st = conn.createStatement();
            //执行插入操作，executeUpdate方法返回成功的条数
            int num = st.executeUpdate(strSql);
            if (num > 0) {
                System.out.println("插入成功！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //SQL执行完成之后释放相关资源
            JdbcUtils.release(conn, st, rs);
        }
    }


    public void delete(String strSql) {
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            int num = st.executeUpdate(strSql);
            if (num > 0) {
                System.out.println("删除成功！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }


    public void update(String strSql) {
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            int num = st.executeUpdate(strSql);
            if (num > 0) {
                System.out.println("更新成功！!！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

    public void find(String strSql) {
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(strSql);
            while (rs.next()) {
                System.out.print(rs.getString("userid") + "        ");
                System.out.print(rs.getString("name") + "        ");
                System.out.print(rs.getString("sex") + "        ");
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

代码块

JdbcCRUDByPreparedStatement类利用PreparedStatement对象操作数据库（建议使用），例如：

package JDBC_Java_Mysql;

import java.sql.*;

/**
 * Created by dhc on 17-5-2.
 *
 * @ClassName: JdbcCRUDByPreparedStatement
 * @Description: 通过PreparedStatement对象完成对数据库的CRUD操作
 * @author: 网络黑寡妇
 */

/**
 * SQL语句中各个字段的类型如下：
 * +----------+-------------+
 * | Field    | Type        |
 * +----------+-------------+
 * | userid   | int(11)     |
 * | name     | varchar(40) |
 * | sex      | varchar(40) |
 * | Tel      | varchar(60) |
 * +----------+-------------+
 */

public class JdbcCRUDByPreparedStatement{
    private static Connection conn = null;
    private static PreparedStatement st = null;
    private static ResultSet rs = null;

    public void insert(String strSql) {
        try {
            //获取一个数据库连接
            conn = JdbcUtils.getConnection();
            //要执行的SQL命令，SQL中的参数使用?作为占位符
            //String strSql = "insert into users(name,sex) values(?,?)";
            //通过conn对象获取负责执行SQL命令的prepareStatement对象
            st = conn.prepareStatement(strSql);
            //执行插入操作，executeUpdate方法返回成功的条数
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！!！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //SQL执行完成之后释放相关资源
            JdbcUtils.release(conn, st, rs);
        }
    }

    public void delete(String strSql) {
        try {
            conn = JdbcUtils.getConnection();
            st = conn.prepareStatement(strSql);
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("删除成功！!！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

    public void update(String strSql) {
        try {
            conn = JdbcUtils.getConnection();
            st = conn.prepareStatement(strSql);
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("更新成功！！");
            }
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

    public void find(String strSql) {
        try {
            conn = JdbcUtils.getConnection();
            st = conn.prepareStatement(strSql);
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.print(rs.getString("userid") + "        ");
                System.out.print(rs.getString("name") + "        ");
                System.out.print(rs.getString("sex") + "        ");
                System.out.print(rs.getString("Tel") + "        ");
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

代码块

对Mysql数据库的CURD操作测试Code，例如：

package JDBC_Java_Mysql;

/**
 * Created by dhc on 17-5-2.
 * 测试对数据库Mysql的CRUD操作。
 * @author: 网络黑寡妇
 */
public class Jdbc_test{
    public static void main(String[] args) {
        JdbcCRUDByPreparedStatement ps = new JdbcCRUDByPreparedStatement();
        //查询操作
        String strSql = "select * from user";
        ps.find(strSql);
        //JdbcCRUDByStatement ps1=new JdbcCRUDByStatement();
        //ps1.find(strSql);

        //删除操作
        String delSql = "delete from user where userid='4' ";
        //ps.delete(delSql);

        //添加操作
        String insertSql = "insert into user values(3,'xiaohong','w',156666669)";
        //ps.insert(insertSql);

        //更新操作
        String updateSql = "update user set Tel=156666666 where userid=3";
        //ps.update(updateSql);
    }
}