JDBC初学总结（二）

实例1:实现信息的增删改查【Statement】

1.创建数据库表

create table t_user(
user_id int primary key auto_increment,
user_name varchar(20),
user_age int,
user_sex bit,
user_address varchar(30),
user_day datetime
);

2.创建一个java工程
3.导入数据库驱动包
4.创建java实体类

package com.wangxing.bean;
import java.util.Date;
/**
 * 保存用户信息的java实体类
 * @author Administrator
 *
 */
public class UserBean {
	private int userid;
	private String username;
	private int userage;
	private boolean usersex;
	private String useraddress;
	private Date  userday;
	public int getUserid() {
		return userid;
	}
	public void setUserid(int userid) {
		this.userid = userid;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public int getUserage() {
		return userage;
	}
	public void setUserage(int userage) {
		this.userage = userage;
	}
	public boolean isUsersex() {
		return usersex;
	}
	public void setUsersex(boolean usersex) {
		this.usersex = usersex;
	}
	public String getUseraddress() {
		return useraddress;
	}
	public void setUseraddress(String useraddress) {
		this.useraddress = useraddress;
	}
	public Date getUserday() {
		return userday;
	}
	public void setUserday(Date userday) {
		this.userday = userday;
	}
}

5.创建连接数据库的java类

package com.wangxing.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
 * 数据库连接类
 * @author Administrator
 *
 */
public class DBConnection {
	//定义数据库驱动名称
	private  static final String DRIVER_NAME="com.mysql.jdbc.Driver";
	//定义数据库url
	private  static final String URL="jdbc:mysql://127.0.0.1:3306/test";
	//定义数据库用户名
	private  static final String USERNAME="root";
	//定义数据库密码
	private  static final String PASSWORD="123456";
	//通过静态代码块加载数据库驱动
	static {
		try {
			Class.forName(DRIVER_NAME);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 得到数据库连接
	 */
	public static Connection getConnection() {
		Connection  conn=null;
		try {
			conn=DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

6.创建数据库访问类

package com.wangxing.dbacsess;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.wangxing.bean.UserBean;
import com.wangxing.db.DBConnection;

/**
 * 数据库用户信息的访问类
 * @author Administrator
 *
 */
public class UserAcesess {
	/**
	 * 添加用户信息
	 */
	public  boolean insertUser(UserBean userbean) {
		boolean flag=false;
		try {
		Connection conn=DBConnection.getConnection();
		String insertsql="insert into t_user values(null,'"+userbean.getUsername()+
				"',"+userbean.getUserage()+","+userbean.isUsersex()+
				",'"+userbean.getUseraddress()+"','"+userbean.getUserday()+"');";
		Statement statement=conn.createStatement();
		int temp=statement.executeUpdate(insertsql);
		if(temp>0) flag=true;
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 修改用户信息
	 */
	public  boolean  updateUser(UserBean userbean) {
		boolean flag=false;
		try {
		Connection conn=DBConnection.getConnection();
		String updatesql="update t_user set user_name='"+userbean.getUsername()+
				"',user_age="+userbean.getUserage()+
				",user_sex="+userbean.isUsersex()+
				",user_address='"+userbean.getUseraddress()+
				"',user_day='"+userbean.getUserday()+
				"' where user_id="+userbean.getUserid()+";";
		Statement statement=conn.createStatement();
		int temp=statement.executeUpdate(updatesql);
		if(temp>0) flag=true;
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 删除用户信息
	 */
	public  boolean  deleteUser(int userid) {
		boolean flag=false;
		try {
		Connection conn=DBConnection.getConnection();
		String deletesql="delete from t_user where user_id="+userid+";";
		Statement statement=conn.createStatement();
		int temp=statement.executeUpdate(deletesql);
		if(temp>0) flag=true;
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 查询所有用户信息
	 */
	public  List<UserBean>  selectUser() {
		List<UserBean> userlist=null;
		try {
		Connection conn=DBConnection.getConnection();
		String selectsql="select * from t_user;";
		Statement statement=conn.createStatement();
		ResultSet rs=statement.executeQuery(selectsql);
		userlist=new ArrayList<UserBean>();
		while(rs.next()) {
			int userid=rs.getInt("user_id");
			String username=rs.getString("user_name");
			int userage=rs.getInt("user_age");
			boolean usersex=rs.getBoolean("user_sex");
			String useraddress=rs.getString("user_address");
			Date userday=rs.getDate("user_day");
			UserBean user=new UserBean();
			user.setUserid(userid);
			user.setUsername(username);
			user.setUserage(userage);
			user.setUsersex(usersex);
			user.setUseraddress(useraddress);
			user.setUserday(userday);
			userlist.add(user);
		}
		rs.close();
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return userlist;
	}
	/**
	 * 根据userid查询用户信息
	 */
	public  UserBean  selectUserById(int userid) {
		UserBean user=null;
		try {
		Connection conn=DBConnection.getConnection();
		String selectsql="select * from t_user where user_id="+userid+";";
		Statement statement=conn.createStatement();
		ResultSet rs=statement.executeQuery(selectsql);
		if(rs.next()) {
			int id=rs.getInt("user_id");
			String username=rs.getString("user_name");
			int userage=rs.getInt("user_age");
			boolean usersex=rs.getBoolean("user_sex");
			String useraddress=rs.getString("user_address");
			Date userday=rs.getDate("user_day");
			user=new UserBean();
			user.setUserid(id);
			user.setUsername(username);
			user.setUserage(userage);
			user.setUsersex(usersex);
			user.setUseraddress(useraddress);
			user.setUserday(userday);
		}
		rs.close();
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return user;
	}
}

实例2:实现信息的增删改查【PreparedStatement】

package com.wangxing.dbacsess;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.wangxing.bean.UserBean;
import com.wangxing.db.DBConnection;

/**
 * 数据库用户信息的访问类
 * @author Administrator
 *
 */
public class UserAcesess {
	/**
	 * 添加用户信息
	 */
	public  boolean insertUser(UserBean userbean) {
		boolean flag=false;
		try {
		Connection conn=DBConnection.getConnection();
		String insertsql="insert into t_user values(null,?,?,?,?,?);";
		PreparedStatement statement=conn.prepareStatement(insertsql);
		statement.setString(1, userbean.getUsername());
		statement.setInt(2, userbean.getUserage());
		statement.setBoolean(3, userbean.isUsersex());
		statement.setString(4, userbean.getUseraddress());
		statement.setDate(5, userbean.getUserday());
		int temp=statement.executeUpdate();
		if(temp>0) flag=true;
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 修改用户信息
	 */
	public  boolean  updateUser(UserBean userbean) {
		boolean flag=false;
		try {
		Connection conn=DBConnection.getConnection();
		String updatesql="update t_user set user_name=?,user_age=?,user_sex=?,user_address=?,user_day=? where user_id=?;";
		PreparedStatement statement=conn.prepareStatement(updatesql);
		statement.setString(1, userbean.getUsername());
		statement.setInt(2, userbean.getUserage());
		statement.setBoolean(3, userbean.isUsersex());
		statement.setString(4, userbean.getUseraddress());
		statement.setDate(5, userbean.getUserday());
		statement.setInt(6, userbean.getUserid());
		int temp=statement.executeUpdate();
		if(temp>0) flag=true;
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 删除用户信息
	 */
	public  boolean  deleteUser(int userid) {
		boolean flag=false;
		try {
		Connection conn=DBConnection.getConnection();
		String deletesql="delete from t_user where user_id=?;";
		PreparedStatement statement=conn.prepareStatement(deletesql);
		statement.setInt(1,userid);
		int temp=statement.executeUpdate();
		if(temp>0) flag=true;
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 查询所有用户信息
	 */
	public  List<UserBean>  selectUser() {
		List<UserBean> userlist=null;
		try {
		Connection conn=DBConnection.getConnection();
		String selectsql="select * from t_user;";
		PreparedStatement statement=conn.prepareStatement(selectsql);
		ResultSet rs=statement.executeQuery();
		userlist=new ArrayList<UserBean>();
		while(rs.next()) {
			int userid=rs.getInt("user_id");
			String username=rs.getString("user_name");
			int userage=rs.getInt("user_age");
			boolean usersex=rs.getBoolean("user_sex");
			String useraddress=rs.getString("user_address");
			Date userday=rs.getDate("user_day");
			UserBean user=new UserBean();
			user.setUserid(userid);
			user.setUsername(username);
			user.setUserage(userage);
			user.setUsersex(usersex);
			user.setUseraddress(useraddress);
			user.setUserday(userday);
			userlist.add(user);
		}
		rs.close();
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return userlist;
	}
	/**
	 * 根据userid查询用户信息
	 */
	public  UserBean  selectUserById(int userid) {
		UserBean user=null;
		try {
		Connection conn=DBConnection.getConnection();
		String selectsql="select * from t_user where user_id=?;";
		PreparedStatement statement=conn.prepareStatement(selectsql);
		statement.setInt(1, userid);
		ResultSet rs=statement.executeQuery();
		if(rs.next()) {
			int id=rs.getInt("user_id");
			String username=rs.getString("user_name");
			int userage=rs.getInt("user_age");
			boolean usersex=rs.getBoolean("user_sex");
			String useraddress=rs.getString("user_address");
			Date userday=rs.getDate("user_day");
			user=new UserBean();
			user.setUserid(id);
			user.setUsername(username);
			user.setUserage(userage);
			user.setUsersex(usersex);
			user.setUseraddress(useraddress);
			user.setUserday(userday);
		}
		rs.close();
		statement.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return user;
	}
}