程序练习题(4)

---

JDBC访问数据库

理解JDBC的工作原理，掌握JDBC访问数据库； 掌握常见数据库MYSQL；

---

实验环境

Win7+Myeclipse+MySQL

导入MySQL驱动到项目目录下

---

实验内容

使用JDBC写一个简单的小程序，JDBC连接MySQL数据库，并且进行一些基本操作。
创建数据库:

查询全部:
特定查询:
插入行:
删除行:

更新行:

---

实验代码

MySQL里面创建数据库：

CREATE TABLE
    tbl_user_info
    (
        id INT NOT NULL AUTO_INCREMENT,
        user_name VARCHAR(20) NOT NULL,
        age INT NOT NULL,
        sex INT(1) NOT NULL,
        create_dt DATE NOT NULL,
        PRIMARY KEY (id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

UserVO.java

package 项目名;
import java.util.Date;
public class UserVO {
	private int id;
	private String userName;
	private int age;
	private int sex;
	private Date createDt;
	
	public int getId(){
		return id;
	}
	public void setId(int id){
		this.id=id;
	}
	public String getUserName(){
		return userName;
	}
	public void setUserName(String userName){
		this.userName=userName;
	}
	public int getAge(){
		return age;
	}
	public void setAge(int age){
		this.age=age;
	}
	public int getSex(){
		return sex;
	}
	public void setSex(int sex){
		this.sex=sex;
	}
	public Date getCreateDt(){
		return createDt;
	}
	public void setCraeteDt(Date createDt){
		this.createDt=createDt;
	}
	public String toString(){
		return "UserVO [id="+id+",userName="+userName+",age="+age+",sex="+sex+",createDt="+createDt+"]";
	}
	
}

DBUtil.java

package 项目名;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtil {
	private static final String UR1="jdbc:mysql://localhost:3306/test?"+"user=Mysql&useUnicode=true&characterEncoding=UTF-8";//test为数据库名称，我的Mysql账户没设置密码
	private static final String DRIVER="com.mysql.jdbc.Driver";
	public static Connection connectDB()throws Exception{
		Class.forName(DRIVER);
		Connection conn=DriverManager.getConnection(UR1);
		return conn;
	}
	
}

UserDao.java

package 项目名;
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.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao {

		public List<UserVO>queryAll()throws Exception{
			Connection conn=DBUtil.connectDB();
			String sql="SELECT * FROM tbl_user_info";
			List<UserVO>userList=new ArrayList<UserVO>();
			Statement stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				UserVO user= new UserVO();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("user_name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getInt("sex"));
				user.setCraeteDt(rs.getDate("create_dt"));
				userList.add(user);
			}
			return userList;
		}

		public List<UserVO>queryByParams(List<Map<String,Object>>params)throws Exception{
			Connection conn=DBUtil.connectDB();
			StringBuilder sql=new StringBuilder("SELECT * FROM tbl_user_info WHERE 1=1 ");
			for(Map<String,Object>param:params){
				sql.append("and");
				sql.append(" "+param.get("col")+" ");
				sql.append(" "+param.get("rel")+" ");
				sql.append(" "+param.get("value")+" ");
			} 
			System.out.println(sql.toString());
			List<UserVO>userList=new ArrayList<UserVO>();
			Statement stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql.toString());
			while(rs.next()){
				UserVO user= new UserVO();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("user_name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getInt("sex"));
				user.setCraeteDt(rs.getDate("create_dt"));
				userList.add(user);
			}
			return userList;
		}
	public void addUser(UserVO user)throws Exception{
		Connection conn=DBUtil.connectDB();
		String sql="INSERT INTO tbl_user_info(user_name,age,sex,create_dt)"+" VALUES(?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1,user.getUserName());
		pstmt.setInt(2,user.getAge());
		pstmt.setInt(3,user.getSex());
		pstmt.setDate(4,new Date(new java.util.Date().getTime()));
		pstmt.execute();
	}
	public void deleteUser(int id)throws Exception{
		Connection conn=DBUtil.connectDB();
		String sql="DELETE FROM tbl_user_info WHERE id=?";
		PreparedStatement pstmt=conn.prepareStatement(sql);
		pstmt.setInt(1, id);
		pstmt.execute();
	}
	public void updateUser(UserVO user)throws Exception{
		Connection conn=DBUtil.connectDB();
		String sql="UPDATE tbl_user_info SET user_name=?,age=?,sex=?"+" WHERE id=?";
		PreparedStatement pstmt=conn.prepareStatement(sql);
		pstmt.setString(1,user.getUserName());
		pstmt.setInt(2, user.getAge());
		pstmt.setInt(3, user.getSex());
		pstmt.setInt(4,user.getId());
		pstmt.executeUpdate();
	}
	public static void main(String[] args){
		UserDao dao=new UserDao();
		try{
			List<UserVO>userList=dao.queryAll();
			for(UserVO user:userList){
				System.out.println(user);
			}
		}
			catch(Exception e){
				e.printStackTrace();
			}
			
		List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();
		Map<String,Object>param1 =new HashMap<String,Object>();
		param1.put("col","user_name");
		param1.put("rel","like");
		param1.put("value","'%John%'");
		params.add(param1);
		
		Map<String,Object>param2=new HashMap<String,Object>();
		param2.put("col","sex");
		param2.put("rel","=");
		param2.put("value",1);
		params.add(param2);
		try{
			List<UserVO>userList=dao.queryByParams(params);
			for(UserVO user:userList){
				System.out.println(user);
			}
		}
			catch(Exception e){
				e.printStackTrace();
			}
		UserVO user= new UserVO();
		user.setUserName("Tom");
		user.setAge(20);
		user.setSex(1);
		try{
			dao.addUser(user);
		}
		catch(Exception e){
			e.printStackTrace();
		}
		try{
			dao.deleteUser(7);//根据id删除
		}
		catch(Exception e){
			e.printStackTrace();
		}
		user.setUserName("Mary");
		user.setAge(30);
		user.setSex(0);
		user.setId(4);
		try{
			dao.updateUser(user);
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}				
}