一：jdbc
（1） 注册驱动
（2）获得链接：
（3）获得sql 容器： Statement ：
（4）执行sql 语句：
（5）查询操作， 需要遍历结果集：
（6）关闭资源：
Statement： 存在的弊端， 可以被sql 注入：
所以实际开发是不在地用的
PreparedStatement: 类：
简单介绍：
java.sql包中的**PreparedStatement 接口继承了Statement，**并与之在两方面有所不同：有人主张，在JDBC应用中，如果你已经是稍有水平开发者，你就应该始终以PreparedStatement代替Statement.也就是说，在任何时候都不要使用Statement。
作用：
（1）带有预编译的功能：
（2）效率高：
（3）防止sql 注入：
传统的方式： 当执行到sql 语句的时候，sql 语句才能够被编译， 执行：
stmt = conn.createStatement();
String sql =“select * from student where password =’+password+’” and username=’"+username+"’;
stmt.execuete(sql);
预编译：
String sql =“select * from student where password = ? and username =?”;
conn.prepareStatement(String sql); 获得容器的时候， sql 给定： sql预编译：
占位符有几个参数就设置几个，student类的dao层----------增删改查的方法如下：
package com.yidongxueyuan.dao.impl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yidongxueyuan.dao.StudentDao;
import com.yidongxueyuan.domain.Student;
import com.yidongxueyuan.utils.JdbcUtil;
public class StudentDaoImpl implements StudentDao {
@Override
public void saveStudent(Student stu) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
try {
String sql ="insert into student(sname, birthday) values(?,?)";
stmt = conn.prepareStatement(sql);
//通过预编译对象： 给占位符进行设置值：
stmt.setString(1, stu.getSname());
stmt.setDate(2, stu.getBirthday());
//语句的执行： 一定要放在 设置占位符之后：
int num = stmt.executeUpdate();
System.out.println(num);
} catch (SQLException e) {
e.printStackTrace();
} finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Override
public void deleteStudentById(String id) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
try {
String sql ="delete from student where sid=?";
stmt = conn.prepareStatement(sql);
//设置占位符：
stmt.setString(1, id);
//语句的执行： 一定要放在 设置占位符之后：
int num = stmt.executeUpdate();
System.out.println(num);
} catch (SQLException e) {
e.printStackTrace();
} finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Override
public void updateStudent(Student stu) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
try {
String sql ="update student set sname=? where sid=?";
stmt = conn.prepareStatement(sql);
//设置占位符：
stmt.setString(1, stu.getSname());
stmt.setString(2, stu.getSid());
//语句的执行： 一定要放在 设置占位符之后：
int num = stmt.executeUpdate();
System.out.println(num);
} catch (SQLException e) {
e.printStackTrace();
} finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Override
public Student findById(String id) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
ResultSet rs=null;
try {
String sql =" select * from student where sid=?";
stmt = conn.prepareStatement(sql);
//设置占位符：
stmt.setString(1, id);
//语句的执行： 一定要放在 设置占位符之后：
rs = stmt.executeQuery();
if(rs.next()){
Student stu = new Student();
String sid = rs.getString("sid");
String name = rs.getString("sname");
Date date = rs.getDate("birthday");
stu.setSid(sid);
stu.setSname(name);
stu.setBirthday(date);
return stu;
}
return null;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally{
JdbcUtil.release(rs, stmt, conn);
}
}
@Override
public List<Student> findAll() {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
ResultSet rs=null;
try {
String sql =" select * from student ";
stmt = conn.prepareStatement(sql);
//语句的执行： 一定要放在 设置占位符之后：
rs = stmt.executeQuery();
List<Student> list = new ArrayList<Student>();
while(rs.next()){
Student stu = new Student();
String sid = rs.getString("sid");
String name = rs.getString("sname");
Date date = rs.getDate("birthday");
stu.setSid(sid);
stu.setSname(name);
stu.setBirthday(date);
list.add(stu);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}
二： 案例：
(1)加了+
访问当前项目的： http://localhost:8080/javaEE-18/index.jsp ----》登录：
注册： ------》 登录： ------》 展示了所有的用户信息： id username password ：
(2)
列表的展示： 查询所有：
查询： 根据id 进行查询：
修改： 先根据id 将对象进行查询， 展示在页面上， 修改完成后， 进行save
添加：
删除： 根据id 进行删除：
批量删除：
（3） 底层数据库的搭建：
实现步骤：
创建数据库表：
创建一个customer 表：
CREATE TABLE Customers(
id VARCHAR (100) PRIMARY KEY,
NAME VARCHAR(100),
gender VARCHAR(10),
birthday DATE ,
phonenum VARCHAR(100),
email VARCHAR(100),
hobby VARCHAR(255),
TYPE VARCHAR(10),
description LONGTEXT
)
接着是mvc三层：
dao层：
接口：
package com.yidongxueyuan.dao;
import java.util.List;
import com.yidongxueyuan.domain.Customer;
public interface CustomerDao {
List<Customer> findAll();
Customer findById(Customer cus);
void updateCustomer(Customer customer);
void deleteById(Customer cus);
void addCustomer(Customer customer);
}
实现类：
package com.yidongxueyuan.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yidongxueyuan.dao.CustomerDao;
import com.yidongxueyuan.domain.Customer;
import com.yidongxueyuan.utils.JdbcUtil;
public class CustomerDaoImpl implements CustomerDao {
@Override
public List<Customer> findAll() {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
ResultSet rs=null;
try {
String sql =" select * from Customers ";
stmt = conn.prepareStatement(sql);
//语句的执行： 一定要放在 设置占位符之后：
rs = stmt.executeQuery();
List<Customer> list = new ArrayList<Customer>();
while(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setPhonenum(rs.getString("phonenum"));
c.setHobby(rs.getString("hobby"));
c.setEmail(rs.getString("email"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
list.add(c);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally{
JdbcUtil.release(rs, stmt, conn);
}
}
@Override
public Customer findById(Customer cus) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
ResultSet rs=null;
try {
String sql =" select * from Customers where id=?";
stmt = conn.prepareStatement(sql);
//设置占位符：
stmt.setString(1, cus.getId());
//语句的执行： 一定要放在 设置占位符之后：
rs = stmt.executeQuery();
if(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setPhonenum(rs.getString("phonenum"));
c.setHobby(rs.getString("hobby"));
c.setEmail(rs.getString("email"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
return c;
}
return null;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally{
JdbcUtil.release(rs, stmt, conn);
}
}
@Override
public void updateCustomer(Customer customer) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
try {
String sql ="update customers set NAME=?, gender=?,birthday=?,phonenum=?,email=?,hobby=?,TYPE=?,description=? where id=?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, customer.getName());
stmt.setString(2, customer.getGender());
stmt.setDate(3, new java.sql.Date(customer.getBirthday().getTime()));
stmt.setString(4, customer.getPhonenum());
stmt.setString(5, customer.getEmail());
stmt.setString(6, customer.getHobby());
stmt.setString(7, customer.getType());
stmt.setString(8, customer.getDescription());
stmt.setString(9, customer.getId());
//语句的执行： 一定要放在 设置占位符之后：
int num = stmt.executeUpdate();
System.out.println(num);
} catch (SQLException e) {
e.printStackTrace();
} finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Override
public void deleteById(Customer cus) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
try {
String sql ="delete from customers where id=? ";
stmt = conn.prepareStatement(sql);
//设置占位符：
stmt.setString(1, cus.getId());
//语句的执行： 一定要放在 设置占位符之后：
int num = stmt.executeUpdate();
System.out.println(num);
} catch (SQLException e) {
e.printStackTrace();
} finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Override
public void addCustomer(Customer customer) {
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt=null;
try {
String sql =" insert into customers(id,NAME, gender,birthday,phonenum,email,hobby,TYPE,description) " +
"values(?,?,?,?,?,?,?,?,?)";
stmt = conn.prepareStatement(sql);
//通过预编译对象： 给占位符进行设置值：
stmt.setString(1, customer.getId());
stmt.setString(2, customer.getName());
stmt.setString(3, customer.getGender());
stmt.setDate(4, new java.sql.Date(customer.getBirthday().getTime()));
stmt.setString(5, customer.getPhonenum());
stmt.setString(6, customer.getEmail());
stmt.setString(7, customer.getHobby());
stmt.setString(8, customer.getType());
stmt.setString(9, customer.getDescription());
//语句的执行： 一定要放在 设置占位符之后：
int num = stmt.executeUpdate();
System.out.println(num);
} catch (SQLException e) {
e.printStackTrace();
} finally{
JdbcUtil.release(null, stmt, conn);
}
}
}
service层：
接口：
package com.yidongxueyuan.service;
import java.util.List;
import com.yidongxueyuan.domain.Customer;
/*
* 设计业务接口：
*/
public interface BusinessService {
/**
* 添加的方法：
* @param customer 传递的是customer 对象：
*/
void addCustmer(Customer customer);
/**
* 删除的方法：
* @param id
*/
void deleteById(Customer id);
/**
* 修改的方法：
* @param customer
*/
void updateCustomer(Customer customer);
/**
* 唯一性查询查询方法：
* @param id
* @return
*/
Customer findById(Customer id);
/**
* 查询所有：
* @return
*/
List<Customer> findAll();
}
实现类：
package com.yidongxueyuan.service.impl;
import java.util.List;
import com.yidongxueyuan.dao.CustomerDao;
import com.yidongxueyuan.dao.impl.CustomerDaoImpl;
import com.yidongxueyuan.domain.Customer;
import com.yidongxueyuan.service.BusinessService;
/**
* 业务层的实现类：
* @author Mrzhang
* @version 2.0
* @See customer1.0
*
*/
public class BusinessServiceImpl implements BusinessService {
//依赖dao层：
private CustomerDao dao = new CustomerDaoImpl();
@Override
public void addCustmer(Customer customer) {
if(customer == null){
throw new IllegalArgumentException("customer对象不能为null");
}
dao.addCustomer(customer);
}
@Override
public void deleteById(Customer cus) {
if(cus.getId() == null || cus.getId().trim().equals("")){
throw new IllegalArgumentException("customer的id" +
" 必须填写， 不能为空");
}
dao.deleteById(cus);
}
@Override
public void updateCustomer(Customer customer) {
if(customer == null){
throw new IllegalArgumentException("customer对象不能为null");
}
dao.updateCustomer(customer);
}
@Override
public Customer findById(Customer cus) {
if(cus.getId() == null || cus.getId().trim().equals("")){
throw new IllegalArgumentException("customer的id" +
" 必须填写， 不能为空");
}
Customer customer = dao.findById(cus);
return customer;
}
@Override
public List<Customer> findAll() {
List<Customer> list = dao.findAll();
return list;
}
}
测试service层：
package com.yidongxueyuan.test;
import java.util.Date;
import java.util.List;
import org.junit.Test;
import com.yidongxueyuan.domain.Customer;
import com.yidongxueyuan.service.BusinessService;
import com.yidongxueyuan.service.impl.BusinessServiceImpl;
public class CustomerDaoImplTest {
private BusinessService s= new BusinessServiceImpl();
//增加：
@Test
public void test1() throws Exception {
Customer c= new Customer("1002", "宋坤2", "男", new Date(), "18811307278", "243114798@qq.com", "女", "svip", "好男人");
s.addCustmer(c);
}
//唯一性查询：
@Test
public void test2() throws Exception {
Customer cus=new Customer();
cus.setId("1001");
Customer c = s.findById(cus);
System.out.println(c);
}
//全查询：
@Test
public void test3() throws Exception {
List<Customer> list = s.findAll();
System.out.println(list);
}
//删除
@Test
public void test4() throws Exception {
Customer cus=new Customer();
cus.setId("1001");
s.deleteById(cus);
}
//更新
@Test
public void test5() throws Exception {
Customer cus=new Customer();
cus.setId("1002");
Customer c = s.findById(cus);
c.setName("飞鹏");
s.updateCustomer(c);
}
}
扫一扫
970
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
