JDBC链接数据库

一。导入jdbc链接需要的驱动包mysql-connector-java-xxx-bin.jar

二。编写jdbc链接工具具体数据库操作留给控制层

import java.sql.Connection;
import java.sql.DriverManager;
/**
 * jdbc链接数据库工具类
 * @author lingyumin
 *
 */
public class DBUtil {
 	public static Connection conn;
 	public static Connection getConn() {
 		try {
 			
 			String username = "root";
 			String password = "cc77";
 			Class.forName("com.mysql.jdbc.Driver");//加载驱动
 			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test_db",username,password);//链接数据库
 			return conn;
 			
 		} catch (Exception e) {
 			e.printStackTrace();
 			return null;
 		}
 	}
}

这两句是核心啊别忘了

的Class.forName（ “com.mysql.jdbc.Driver”）; //加载驱动

conn = DriverManager.getConnection（“jdbc：mysql：// localhost：3306 / jdbc_ test_db”，用户名，密码）; //链接数据库

 

三。在模型层从创建与数据库对应的模型 

import java.util.Date;
public class People {
 	Integer id;
 	String name;
 	Integer age;
 	Date birthDay;
 	public Integer getId() {
 		return id;
 	}
 	public void setId(Integer id) {
 		this.id = id;
 	}
 	public String getName() {
 		return name;
 	}
 	public void setName(String name) {
 		this.name = name;
 	}
 	public Integer getAge() {
 		return age;
 	}
 	public void setAge(Integer age) {
 		this.age = age;
 	}
 	public Date getBirthDay() {
 		return birthDay;
 	}
 	public void setBirthDay(Date birthDay) {
 		this.birthDay = birthDay;
 	}
}

四.DAO控制层编写模型与数据库交互逻辑（凝乳）

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import model.People;
import util.DBUtil;
/**
 * 控制层 对数据进行curd
 * @author lingyumin
 *
 */
public class PeopleDAO {
 	//向数据库增加
 	public void addPeople(People p){
 		Connection conn = DBUtil.getConn();
 		try {
 			//预编译
 			PreparedStatement pstmt = conn.prepareStatement("insert into people(name,age,birthday) values(?,?,?)");
 			
 			pstmt.setString(1, p.getName());
 			pstmt.setInt(2, p.getAge());
 			pstmt.setDate(3, new Date(p.getBirthDay().getTime()));
 			
 			pstmt.executeUpdate();
 			System.out.println("向数据库添加成功");
 		} catch (Exception e) {
 			e.printStackTrace();
 			System.out.println("向数据库添加失败");
 		}
 	}
 	
 	//向数据库删除
 	public void delPeople(int id) {
 		Connection conn = DBUtil.getConn();
 		try {
 			PreparedStatement pstmt = conn.prepareStatement("delete from people where id=?");
 			pstmt.setInt(1, id);
 			
 			pstmt.executeUpdate();
 			System.out.println("向数据库删除成功");
 		} catch (Exception e) {
 			e.printStackTrace();
 			System.out.println("向数据库删除失败");
 		}
 	}
 	
 	//向数据库更改
 	public void updatePeople(People p,int id) {
 		Connection conn = DBUtil.getConn();
 		try {
 			PreparedStatement pstmt = conn.prepareStatement("update people set name=?,age=?,birthday=? where id=?");
 			pstmt.setString(1, p.getName());
 			pstmt.setInt(2, p.getAge());
 			pstmt.setDate(3, new Date(p.getBirthDay().getTime()));
 			pstmt.setInt(4, id);
 			
 			pstmt.executeUpdate();
 			System.out.println("向修改添加成功");
 		} catch (Exception e) {
 			e.printStackTrace();
 			System.out.println("向数据库修改失败");
 		}
 	}
 	
 	//向数据库查找
 	public People getPeople(int id) {
 		People p = null;
 		
 		Connection conn = DBUtil.getConn();
 		try {
 			PreparedStatement pstmt = conn.prepareStatement("select * from people where id=?");
 			pstmt.setInt(1, id);
 			
 			ResultSet rs = pstmt.executeQuery();
 			if(rs.next()) {
 				p=new People();
 				p.setId(rs.getInt("id"));
 				p.setName(rs.getString("name"));
 				p.setAge(rs.getInt("age"));
 				p.setBirthDay(rs.getDate("birthday"));
 			}
 			return p;
 		} catch (Exception e) {
 			e.printStackTrace();
 			return null;
 		}
 	}
 	
 	//获取所有对象
 	public List getAllPeople() throws SQLException {
 		List<People> ps = new ArrayList<People>();
 		
 		Connection conn = DBUtil.getConn();
 		Statement st=conn.createStatement();
 		ResultSet rs = st.executeQuery("select * from people");
 		while(rs.next()) {
 			People p = new People();
 			p.setId(rs.getInt("id"));
 			p.setName(rs.getString("name"));
 			p.setAge(rs.getInt("age"));
 			p.setBirthDay(rs.getDate("birthday"));
 			ps.add(p);
 		}
 		return ps;
 		
 	}
 	
 	public static void main(String[] args) throws SQLException {
 		List<People> ps = new PeopleDAO().getAllPeople();
 		for(People p:ps) {
 			System.out.print(p.getId()+" "+p.getName()+" "+p.getAge()+" "+p.getBirthDay());
 			System.out.println();
 		}
 		
//		Calendar c = Calendar.getInstance();
//		People p = new People("小猪",10,c.getTime());
 		People p = new PeopleDAO().getPeople(3);
 		System.out.print(p.getId()+" "+p.getName()+" "+p.getAge()+" "+p.getBirthDay());
 	}
}

五.PreparedStatement和声明区别和关系

1.PreparedStatement继承于声明。

2.PreparedStatement可以使用占位符，声明不行。

3.PreparedStatement是预编译SQL语句存储在对象中，而语句是用于执行静态SQL语句，对于批量处理PreparedStatement的效率更高。

4.二者在代码编写上不同

PreparedStatement的：

PreparedStatement pstmt = conn.prepareStatement（“select * from where where where =？”）;

pstmt.setInt（1，id）;

ResultSet rs = pstmt.executeQuery（）;

 

声明：

Statement st = conn.createStatement（）;

ResultSet rs = st.executeQuery（“select * from people”）;

5.应尽量使用的PreparedStatement