实例：简单的JDBC复习+MySql入门学习

万变不离其宗。。持久层怎么变也是JDBC，框架怎么新也是反射机制。。

 

今天刚好复习一下JDBC，顺便对MySql进行一个入门的学习。

 

环境：MySql 5.5 + Navicat for MySql 10.0.5 + MyEclipse 9.0

 

从MySql官方：http://www.mysql.com/ 下载了 mysql-connector-java-5.1.17-bin.jar

 

从MyEclipse的DB Browser中得到测试成功后的

 

驱动类：com.mysql.jdbc.Driver

链接URL：jdbc:mysql://localhost:3306/accp

 

准备的差不多了，实例就是 简单粗暴，直接有效 直接上代码。。

 

---------------------------------------我是华丽的无所不在的分割线-------------------------------------------

 

 

用户实体类：

package com.accp.jdbc.entity;

/**
 * 
 * @author Maxpin on 2011-10-04
 * 
 *         用户实体类
 */
public class Userinfo {
	private int userid; // 编号
	private String loginid; // 用户名
	private String loginpwd; // 密码
	private String username; // 姓名

	/**
	 * 构造方法
	 */
	public Userinfo() {
	}

	/**
	 * @param loginid
	 * @param loginpwd
	 * @param username
	 */
	public Userinfo(String loginid, String loginpwd, String username) {
		this.loginid = loginid;
		this.loginpwd = loginpwd;
		this.username = username;
	}

	/**
	 * @param userid
	 * @param loginid
	 * @param loginpwd
	 * @param username
	 */
	public Userinfo(int userid, String loginid, String loginpwd, String username) {
		this.userid = userid;
		this.loginid = loginid;
		this.loginpwd = loginpwd;
		this.username = username;
	}

	//getter & setter methods 略 

 

 

Dao基类：包含了数据库链接、关闭、CRUD操作及MySql分页查询

package com.accp.jdbc.base;

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.ArrayList;
import java.util.List;

import com.accp.jdbc.entity.Userinfo;

/**
 * 
 * @author Maxpin on 2011-10-04
 * 
 *         Dao基类：包含了数据库链接、关闭、CRUD操作及MySql分页查询
 */
public class BaseDao {

	// 连接地址
	private static final String url = "jdbc:mysql://localhost:3306/accp";
	// 驱动类
	private static final String driverClass = "com.mysql.jdbc.Driver";
	// 用户名
	private static final String uname = "root";
	// 密码
	private static final String pwd = "admin";

	/**
	 * 获取数据库连接
	 * 
	 * @return 连接对象
	 */
	protected static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driverClass);
			conn = DriverManager.getConnection(url, uname, pwd);
		} catch (ClassNotFoundException e) {
			System.out.println("找不到驱动类");
		} catch (SQLException e) {
			System.out.println("建立连接错误！");
		}
		return conn;
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 *            数据库连接
	 * @param rs
	 *            结果集
	 * @param pstmt
	 *            命令对象
	 */
	public static void closeAll(Connection conn, ResultSet rs, Statement pstmt) {
		try {
			if (null != rs && !rs.isClosed()) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			System.out.println("关闭结果集出错！");
		}
		try {
			if (null != pstmt && !pstmt.isClosed()) {
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			System.out.println("关闭命令对象出错！");
		}
		try {
			if (null != conn && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			System.out.println("关闭链接出错");
		}
	}

	/**
	 * 保存指定用户信息
	 * 
	 * @param user
	 *            用户对象
	 * @throws Exception
	 *             抛出异常
	 */
	public static void saveUserinfo(Userinfo user) throws Exception {
		if (null != user) {
			Connection conn = getConnection();
			PreparedStatement pstmt = null;
			String sql = "insert into USERINFO values(null,?,?,?)";
			try {
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, user.getLoginid());
				pstmt.setString(2, user.getLoginpwd());
				pstmt.setString(3, user.getUsername());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeAll(conn, null, pstmt);
			}
		} else {
			throw new Exception("用户信息不能为空");
		}
	}

	/**
	 * 删除指定用户信息
	 * 
	 * @param user
	 *            用户对象
	 * @throws Exception
	 *             抛出异常
	 */
	public static void deleteUserinfo(Userinfo user) throws Exception {
		if (null != user) {
			Connection conn = getConnection();
			PreparedStatement pstmt = null;
			String sql = "delete from USERINFO where userid = ?";
			try {
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, user.getUserid());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeAll(conn, null, pstmt);
			}
		} else {
			throw new Exception("用户信息不能为空");
		}
	}

	/**
	 * 更新指定用户信息
	 * 
	 * @param user
	 *            用户对象
	 * @throws Exception
	 *             抛出异常
	 */
	public static void updateUserinfo(Userinfo user) throws Exception {
		if (null != user) {
			Connection conn = getConnection();
			PreparedStatement pstmt = null;
			String sql = "update USERINFO set loginid = ?,loginpwd = ?,username = ? where userid = ?";
			try {
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, user.getLoginid());
				pstmt.setString(2, user.getLoginpwd());
				pstmt.setString(3, user.getUsername());
				pstmt.setInt(4, user.getUserid());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeAll(conn, null, pstmt);
			}
		} else {
			throw new Exception("用户信息不能为空");
		}
	}

	/**
	 * 查询指定用户信息
	 * 
	 * @param id
	 *            用户编号
	 * @return 用户对象
	 * @throws Exception
	 *             抛出异常
	 */
	public static Userinfo queryUserinfo(int id) throws Exception {
		Userinfo user = null;
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from USERINFO where userid = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				user = new Userinfo(id, rs.getString(2), rs.getString(3),
						rs.getString(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, rs, pstmt);
		}
		return user;
	}

	/**
	 * 分页查询用户信息列表
	 * 
	 * @param currentPage
	 *            要查询页码
	 * @param pageSize
	 *            每页显示条数
	 * @return 用户对象集合
	 * @throws Exception
	 *             抛出异常
	 */
	public static List<Userinfo> queryUserinfoList(int currentPage, int pageSize)
			throws Exception {
		// 计算当前页索引
		int pageIndex = (currentPage - 1) * pageSize;
		List<Userinfo> userList = new ArrayList<Userinfo>();
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// MySql分页可使用limit关键字:select * from tableName limit pageIndex,pageSize
		String sql = "select * from USERINFO limit ?,?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, pageIndex);
			pstmt.setInt(2, pageSize);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				userList.add(new Userinfo(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, rs, pstmt);
		}
		return userList;
	}

}

 

 

测试类：

 

package com.accp.jdbc.test;

import java.util.List;

import com.accp.jdbc.base.BaseDao;
import com.accp.jdbc.entity.Userinfo;

/**
 * 
 * @author Maxpin on 2011-10-04
 * 
 *         测试类
 */
public class Test {

	public static void main(String[] args) {
		try {
			
			/*
			 * MySql中的初始数据：（编号、用户名、密码、姓名）
			 * 1 admin 		123123 	管理员
			 * 2 zhangsan 	123123 	张三
			 * 3 lisi 		123123 	李四
			 * 4 wangwu 	123123	 王五
			 * 
			 */
			
			// 测试保存：赵六
			BaseDao.saveUserinfo(new Userinfo("zhaoliu", "123123", "赵六"));
			// 测试更新：赵六
			BaseDao.updateUserinfo(new Userinfo(5, "zhaoliu", "321321", "赵六2"));
			// 测试删除：王五
			BaseDao.deleteUserinfo(new Userinfo(4, null, null, null));
			// 测试查询：管理员
			Userinfo user = BaseDao.queryUserinfo(1);
			System.out.println(user.getUserid() + " " + user.getLoginid() + " "
					+ user.getLoginpwd() + " " + user.getUsername());
			// 测试分页：查询第2页，每页2条。王五已被删除。
			List<Userinfo> userList = BaseDao.queryUserinfoList(2, 2);
			for (Userinfo u : userList) {
				System.out.println(u.getUserid() + " " + u.getLoginid() + " "
						+ u.getLoginpwd() + " " + u.getUsername());
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

}

 

---------------------------------------我是华丽的无所不在的分割线-------------------------------------------

 

MySql给我的感觉还可以，就是在安装完成后要配置一下my.ini

好在5.5提供了MySQLInstanceConfig.exe可以很方便的进行配置操作。

操作步骤我是参照的 http://www.duote.com/tech/1/2430.html#contentbody 

 

另外：

 

MySql自增列的关键字是：AUTO_INCREMENT

插入数据时，可以选择对该列赋值为 null 即可。

 

MySql分页可使用limit关键字:select * from tableName limit pageIndex,pageSize