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

时间:2021-07-08 01:08:13

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

准备工作

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

  可参见之前的文章:

  http://www.cnblogs.com/mengdd/p/3154638.html

  http://www.cnblogs.com/mengdd/p/3154922.html

准备驱动包

  要用JDBC连接MySQL,需要一个驱动包:

  可以去这里下载(其实完整安装MySQL后可以在安装路径中找到这个包):

  http://www.mysql.com/products/connector/

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

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

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连接不同数据库:

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

  

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

  最后要记得关闭资源。

实践程序

  完整Helper类代码(感谢ZJL同学无私奉献悉心指导):

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);
} }
}

  测试代码:

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();
} } }

  结果:

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

参考资料

  官方文档:

  Java SE Technologies – Database

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

  JDBC Overview

  http://www.oracle.com/technetwork/java/overview-141217.html

  JDBC API

  http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/

  Tutorial JDBC basic

  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

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

  JDBC教程:

  http://www.yiibai.com/jdbc/jdbc-db-connections.html