JAVA-JDBC

包含以下几步:

1. 数据库连接: 加载数据库驱动Class.forName("com.mysql.jdbc.Driver"); ,获得数据连接conn=DriverManager.getConnection(URL, USER, PASSWORD); 

2. 读取数据 : 建立执行对象,建立接收对象并接收执行语句的结果.

3. 关闭数据库

查询与其他操作不同

PreparedStatement 这个预处理类 可以将里面有?的替换掉

注意日期 java.sql.Date 是继承于 java.util.Date 注意用时候转换

连接数据库:

DBUtil

package MVCTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;


public class DBUtil {
	private static String URL="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8";
	private static String USER="root";
	private static String PASSWORD="";
	private static Connection conn;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getConnection(){
		return conn;
	}
}

数据库操作:

DAO

package MVCTest;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

/*
 * 业务处理类
 */
public class DAO {

	public void addUser(User u) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into basic " + "(id,name,age,password,date)" + "values( ?,?,?,?,current_date())";
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		ptmt.setString(1, u.getId());
		ptmt.setString(2, u.getName());
		ptmt.setInt(3, u.getAge());
		ptmt.setString(4, u.getPassword());
		ptmt.execute();
	}
//com.mysql.jdbc.JDBC4PreparedStatement@96b38e: update basic set name='xsy',age=22,password='1234',date='2015-04-09'where id='1'
	public void delUser(User u) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "delete from basic " + "where id=?";
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		ptmt.setString(1,u.getId());//这里字符串的替代
		ptmt.execute();
	}

	public void updateUser(User u) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "update basic" + "set name=?,age=?,password=?,date=?" + "where id=?";
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		ptmt.setString(1, u.getName());
		ptmt.setInt(2, u.getAge());
		ptmt.setString(3, u.getPassword());
		ptmt.setDate(4, new Date(u.getDate().getTime()));   //日期转换,这里要用java.sql.Date类
		ptmt.setString(5, u.getId());  System.out.println(ptmt.toString());//可以看PreparedStatement的处理
		ptmt.execute();
	}

	public List<User> queryUser(String name) throws SQLException {
		User user = null;
		List<User> list = new ArrayList<User>();
		Connection conn = DBUtil.getConnection();
		String sql = "select * from basic where name=?";
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		ptmt.setString(1, name);
		ResultSet re = ptmt.executeQuery();
		while(re.next()){
			user = new User();
			user.setAge(re.getInt("age"));
			user.setId(re.getString("id"));
			user.setName(name);
			user.setDate(re.getDate("date"));
			user.setName(re.getString("name"));
			user.setPassword(re.getString("password"));
			list.add(user);
		}
		
		return list;
	}
	/*
	 * 为了解决参数无限的情况,即按照某类需求查询
	 * 解决了不定参的问题
	 */
	public List<User> queryUser(Map<String,Object> parms) throws SQLException {
		User user = null;
		List<User> list = new ArrayList<User>();
		Connection conn = DBUtil.getConnection();
		StringBuilder sql = new StringBuilder("select * from basic where 1=1 ");//这个技巧避免了and添加的分情况
		if(parms!=null && parms.size()>0){
			for(Map.Entry<String, Object> t:parms.entrySet()){
				sql.append(" and " + t.getKey()+ " = "+t.getValue());
			}
		}
		System.out.println(sql.toString());
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql.toString());
		ResultSet re = ptmt.executeQuery();
		while(re.next()){
			user = new User();
			user.setAge(re.getInt("age"));
			user.setId(re.getString("id"));
			user.setName(re.getString("name"));
			user.setDate(re.getDate("date"));
			user.setName(re.getString("name"));
			user.setPassword(re.getString("password"));
			list.add(user);
		}
		User [] t = list.toArray(new User[0]);
		for(int i = 0;i<t.length;i++)
			System.out.println(t[i]);
		return list;
	}
	public User get(String id) throws SQLException{
		User user =null;
		Connection conn = DBUtil.getConnection();
		String sql = "select * from basic where id=?";
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		ptmt.setString(1, id);
		ResultSet re = ptmt.executeQuery();
		while(re.next()){
			user = new User();
			user.setAge(re.getInt("age"));
			user.setId(id);
			user.setDate(re.getDate("date"));
			user.setName(re.getString("name"));
			user.setPassword(re.getString("password"));
			
		}
		return user;
	}
}

实例类:

package MVCTest;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class User {
	private String name;
	private String password;
	private String id;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	private int age;
	private Date date;
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getAge() {
		return age;
	}
	@Override
	public String toString() {
		return "User [name=" + name + ", password=" + password + ", id=" + id
				+ ", age=" + age + ", date=" + date + "]";
	}
	public void setAge(int age) {
		this.age = age;
	}
	public static void main(String[] args) {
		DAO dao = new DAO();
		System.out.println(Integer.valueOf("234"));
		System.out.println(Integer.parseInt("234"));
		try {
			Map<String,Object> parms = new HashMap<String,Object>();
			parms.put("name", "'xsy'");
			parms.put("id", "'1'");//这个添加要用单引号.list中取出来字符串放入没有单引号,不像PreparedStatement 会自己处理
			dao.queryUser(parms);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}