JDBC连接MySql数据库的简单例子

本blog提供了一个简单JDBC连接数据库的例子，并分别通过Statement和PreparedStatement实现对数据库的查询。

一、为项目添加数据库驱动，本文以MySQL为例

   1）创建一个Java项目（Java Project）,并在项目中建立一个lib文件夹，将下载的mysql驱动复制到该目录下，右 键mysql驱动qu驱动包->BuildPath->Add To Build Path;添加成功！

 2）建立数据库ygw_test,执行以下sql语句，创建user表，并添加测试数据

3）创建属性文件(db.properties)，配置数据库的连接信息

     注意：db.properties必须放在项目的根目录

#mysql DB properties
driver_class=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/ygw_test
username=chaoyue
password=chaoyue

4）建立jdbc连接

package com.chaoyue.jdbc.util;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 该类封装了连接和关闭数据库连接操作
 * 
 * @author 超越
 * @Date 2016年11月30日,下午3:10:10
 * @motto 人在一起叫聚会，心在一起叫团队
 * @Version 1.0
 */
public class DBUtil {
	public static Connection getConnection() {
		Properties prop = new Properties();
		FileInputStream fis = null;
		Connection con = null;
		try {
			fis = new FileInputStream("db.properties");
			prop.load(fis);
			// 加载驱动
			Class.forName(prop.getProperty("driver_class"));
			// 建立连接
			con = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	// 关闭ResultSet
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 关闭PreparedStatement
	public static void closePreparedStatement(PreparedStatement pstm) {
		if (pstm != null) {
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	// 关闭Statement
	public static void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	// 关闭Connection
	public static void closeConnection(Connection con) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

5） 使用Statement和PreparedStatement查询数据

package com.chaoyue.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.chaoyue.jdbc.model.User;
import com.chaoyue.jdbc.util.DBUtil;
/**
 * 使用statement进行查询
 * @author 超越
 * @Date 2016年11月30日,下午3:29:32
 * @motto 人在一起叫聚会，心在一起叫团队
 * @Version 1.0
 */
public class QueryAllUser {
	// 使用statement进行查询
	public static void queryAllByStatement() {
		List<User> userList = new ArrayList<User>();
		// 获得连接
		Connection con = DBUtil.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from user");
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setGender(rs.getString("gender"));
				user.setRegtime(rs.getDate("regtime"));
				System.out.println(user.toString());
				userList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closeStatement(stmt);
			DBUtil.closeConnection(con);
		}
	}

	// 通过id查询用户
	public static void queryAllById(int id) {
		Connection con = DBUtil.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from  user where id =" + id);
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setGender(rs.getString("gender"));
				user.setRegtime(rs.getDate("regtime"));
				System.out.println(user.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closeStatement(stmt);
			DBUtil.closeConnection(con);
		}
	}

	// 使用PreparedStatement进行查询
	public static void queryAllByPreparedStatement() {
		List<User> userList = new ArrayList<User>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from user");
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setGender(rs.getString("gender"));
				user.setRegtime(rs.getDate("regtime"));
				System.out.println(user.toString());
				userList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closeStatement(ps);
			DBUtil.closeConnection(con);
		}
	}

	public static void queryPreparedById(int id) {
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from  user where id=?");
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setGender(rs.getString("gender"));
				user.setRegtime(rs.getDate("regtime"));
				System.out.println(user.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closeStatement(ps);
			DBUtil.closeConnection(con);
		}
	}
}

6) 测试用例