mysql jdbc入门_实例：简单的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 queryUserinfoList(int currentPage, int pageSize)

throws Exception {

// 计算当前页索引

int pageIndex = (currentPage - 1) * pageSize;

List userList = new ArrayList();

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 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可以很方便的进行配置操作。

另外：

MySql自增列的关键字是：AUTO_INCREMENT

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

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

分享到：

2011-10-04 11:26

浏览 4716

评论