JDBC连接MySQL 方法实例及资料收集 JDBC连接MySQL 方法实例及资料收集

﹏ヽ暗。殇╰゛Y 2022-06-03 09:30 296阅读 0赞

## 准备工作 ##

首先，安装MySQL，配置用户名和密码，创建数据库。

可参见之前的文章：

[http://www.cnblogs.com/mengdd/p/3154638.html][http_www.cnblogs.com_mengdd_p_3154638.html]

[http://www.cnblogs.com/mengdd/p/3154922.html][http_www.cnblogs.com_mengdd_p_3154922.html]

# #

## 准备驱动包 ##

要用JDBC连接MySQL，需要一个驱动包：

可以去这里下载（其实完整安装MySQL后可以在安装路径中找到这个包）：

[http://www.mysql.com/products/connector/][http_www.mysql.com_products_connector]

解压后将其中的**mysql-connector-java-XXX-bin.jar**文件加入到Web Project的lib中：

![25182414-a78a3935694446138bd20b45b75c50ee.png][]

## JDBC代码编写 ##

**首先，需要加载驱动：**

// 加载驱动
    Class.forName("com.mysql.jdbc.Driver");

**然后，建立连接：**

String dbUrl = "jdbc:mysql://localhost:3306/mydatabase1";
    String dbUser = "your_username";
    String dbPwd = "your_password";
    // 打开数据库连接
    con = DriverManager.getConnection(dbUrl, dbUser, dbPwd);

注意其中的端口号和数据库名（mydatabase1）可能需要修改。

JDBC连接不同数据库：

![25182400-711c737e0eac4955ac28572c40173678.png][]

之后就可以执行SQL语句了。

最后要记得关闭资源。

## 实践程序 ##

完整Helper类代码（**感谢ZJL同学无私奉献悉心指导**）：

![复制代码][copycode.gif]

package com.meng.utils;
    
    import java.sql.*;
    
    public class JDBCHelper
    {
        private Connection con = null;
        private PreparedStatement stmt = null;
        private ResultSet rs = null;
    
        public JDBCHelper()
        {
    
            try
            {
                // 加载驱动
                Class.forName("com.mysql.jdbc.Driver");
                String dbUrl = "jdbc:mysql://localhost:3306/mydatabase1";
                String dbUser = "mengdd";
                String dbPwd = "881103";
                // 打开数据库连接
                con = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
    
            }
            catch (ClassNotFoundException e)
            {
                System.out.println("Error: unable to load driver class!");
                System.out
                        .println("please check if you hava the mysql-connector jar file in your lib dir!");
                e.printStackTrace();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
    
        }
    
        public ResultSet executeResultSet(String sql)
        {
            return executeResultSet(sql, null);
        }
    
        public ResultSet executeResultSet(String sql, Object[] params)
        {
            try
            {
                // 创建一个JDBC声明
                stmt = con.prepareStatement(sql);
                if (null != params && params.length != 0)
                {
                    for (int i = 0; i < params.length; i++)
                    {
                        stmt.setObject(i + 1, params[i]);
                    }
                }
                rs = stmt.executeQuery();
                return rs;
            }
            catch (Exception ex)
            {
                throw new RuntimeException(ex.getMessage(), ex);
            }
        }
    
        public int executeUpdate(String sql)
        {
            return executeUpdate(sql, null);
        }
    
        public int executeUpdate(String sql, Object[] params)
        {
            try
            {
                // 创建一个JDBC声明
                stmt = con.prepareStatement(sql);
                if (null != params && params.length != 0)
                {
                    for (int i = 0; i < params.length; i++)
                    {
                        stmt.setObject(i + 1, params[i]);
                    }
                }
                return stmt.executeUpdate();
            }
            catch (Exception ex)
            {
                throw new RuntimeException(ex.getMessage(), ex);
            }
        }
    
        public void close()
        {
            try
            {
                // 应该明确地关闭所有的数据库资源
                if (null != rs)
                    rs.close();
                if (null != stmt)
                    stmt.close();
                if (null != con)
                    con.close();
    
            }
            catch (SQLException e)
            {
                throw new RuntimeException(e);
            }
    
        }
    }

![复制代码][copycode.gif]

测试代码：

![复制代码][copycode.gif]

package com.meng.test;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import com.meng.utils.JDBCHelper;
    
    public class Test
    {
        public static void main(String[] args)
        {
            JDBCHelper dbHelper = new JDBCHelper();
    
            ResultSet resultSet = dbHelper.executeResultSet("SELECT * FROM pet;");
    
            try
            {
                while (resultSet.next())
                {
                    System.out.println("-------------");
                    System.out.println("name: " + resultSet.getString("name"));
                    System.out.println("birth: " + resultSet.getString("birth"));
    
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
            finally
            {
                dbHelper.close();
            }
    
        }
    
    }

![复制代码][copycode.gif]

结果：

![复制代码][copycode.gif]

-------------
    name: Tutu
    birth: 2013-03-01
    -------------
    name: Whistler
    birth: 1997-12-09
    -------------
    name: WangWang
    birth: 2008-09-30
    -------------
    name: MiaoMiao
    birth: 2012-01-01

![复制代码][copycode.gif]

## 参考资料 ##

**　　官方文档：**

Java SE Technologies – Database

[http://www.oracle.com/technetwork/java/javase/tech/index-jsp-136101.html][http_www.oracle.com_technetwork_java_javase_tech_index-jsp-136101.html]

JDBC Overview

[http://www.oracle.com/technetwork/java/overview-141217.html][http_www.oracle.com_technetwork_java_overview-141217.html]

JDBC API

[http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/][http_docs.oracle.com_javase_7_docs_technotes_guides_jdbc]

Tutorial JDBC basic

[http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html][http_docs.oracle.com_javase_tutorial_jdbc_basics_index.html]

Getting Started:

[http://docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html][http_docs.oracle.com_javase_1.4.2_docs_guide_jdbc_getstart_GettingStartedTOC.fm.html]

The JDBC classes are contained in the [Java package][] [java.sql][] and [javax.sql][]

JDBC教程：

[http://www.yiibai.com/jdbc/jdbc-db-connections.html][http_www.yiibai.com_jdbc_jdbc-db-connections.html]

[http_www.cnblogs.com_mengdd_p_3154638.html]: http://www.cnblogs.com/mengdd/p/3154638.html
[http_www.cnblogs.com_mengdd_p_3154922.html]: http://www.cnblogs.com/mengdd/p/3154922.html
[http_www.mysql.com_products_connector]: http://www.mysql.com/products/connector/
[25182414-a78a3935694446138bd20b45b75c50ee.png]: /images/20220603/777e616c47824726aeb7052d351c9786.png
[25182400-711c737e0eac4955ac28572c40173678.png]: /images/20220603/3a8ba812409b409d800c0d81f31b4916.png
[copycode.gif]: /images/20220603/1421b7d3a03c4008a4344e105f53a1bc.png
[http_www.oracle.com_technetwork_java_javase_tech_index-jsp-136101.html]: http://www.oracle.com/technetwork/java/javase/tech/index-jsp-136101.html
[http_www.oracle.com_technetwork_java_overview-141217.html]: http://www.oracle.com/technetwork/java/overview-141217.html
[http_docs.oracle.com_javase_7_docs_technotes_guides_jdbc]: http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/
[http_docs.oracle.com_javase_tutorial_jdbc_basics_index.html]: http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
[http_docs.oracle.com_javase_1.4.2_docs_guide_jdbc_getstart_GettingStartedTOC.fm.html]: http://docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html
[Java package]: http://en.wikipedia.org/wiki/Java_package
[java.sql]: http://download.oracle.com/javase/7/docs/api/java/sql/package-summary.html
[javax.sql]: http://download.oracle.com/javase/7/docs/api/javax/sql/package-summary.html
[http_www.yiibai.com_jdbc_jdbc-db-connections.html]: http://www.yiibai.com/jdbc/jdbc-db-connections.html