需求:
对数据库CRUD(增删查改)
建立一个数据库,并插入数据:
createStatement(sql注入):
分为student实例、接口、实现接口、测试四个部分。
Student.java
package cn.student;
public class Student {
private int id;
private String name;
private int age;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
IStudentDao.java
package cn.IStudentDao;
import java.util.List;
import cn.student.Student;
public interface IStudentDao {
//查询全部
List<Student> findAll(Student student);
//保存
void save(Student student);
//更新
void update(Student student);
//删除
void delete(int id);
//主键查询
Student findById(int id);
//分页查询
public List<Student> findId(int index1,int index2);
}
DaoDemo.java
package cn.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.IStudentDao.IStudentDao;
import cn.student.Student;
//实现接口
public class DaoDemo implements IStudentDao{
@Override
public List<Student> findAll(Student student) {
//创建一个集合储存学生对象
List<Student> list = new ArrayList<Student>();
//sql语句命令 SELECT id,NAME,age FROM student_jdbc;
String sql = "SELECT id,NAME,age FROM student_jdbc;";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
Statement state = connection.createStatement();
//实现
ResultSet update = state.executeQuery(sql);
while(update.next()) {
Student stud = new Student();
stud.setId(update.getInt("id"));
stud.setName(update.getString("name"));
stud.setAge(update.getInt("age"));
list.add(stud);
}
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public void save(Student student) {
//sql语句命令 INSERT INTO student_jdbc(NAME,age) VALUES ("miemie2",19);
//Student student = new Student();
String sql = "INSERT INTO student_jdbc(id,NAME,age) VALUES ('"+student.getId()+"','"+student.getName()+"',"+student.getAge()+")";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
Statement state = connection.createStatement();
//实现
int update = state.executeUpdate(sql);
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void update(Student student) {
//sql语句命令 UPDATE student_jdbc SET age=20 WHERE id=1;
String sql = "UPDATE student_jdbc SET age="+student.getAge()+",name='"+student.getName()+"' WHERE id="+student.getId()+";";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
Statement state = connection.createStatement();
//实现
int update = state.executeUpdate(sql);
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void delete(int id) {
//sql语句命令 delete from student_jdbc where id=1;
Student student = new Student();
String sql = "delete from student_jdbc where id="+id+"";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
Statement state = connection.createStatement();
//实现
int update = state.executeUpdate(sql);
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public Student findById(int id) {
//sql语句命令 select id,name,age from student_jdbc where id=1;
Student student = new Student();
String sql = "select name,age from student_jdbc where id="+id+";";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
Statement state = connection.createStatement();
//实现
ResultSet rs = state.executeQuery(sql);
while(rs.next()){
student.setId(id);
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
}
//关闭
rs.close();
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
@Override
public List<Student> findId(int index1, int index2) {
//sql语句命令 select id,name,age from student_jdbc LIMIT 0,2;
List<Student> list = new ArrayList<Student>();
//Student student = new Student();
String sql = "SELECT id,NAME,age FROM student_jdbc LIMIT "+index1+","+index2+";";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
Statement state = connection.createStatement();
//实现
ResultSet rs = state.executeQuery(sql);
while(rs.next()){
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
list.add(student);
}
//关闭
rs.close();
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
TestDemo.java
package cn.test.jdbc;
import static org.junit.Assert.*;
import org.junit.Test;
import cn.IStudentDao.IStudentDao;
import cn.dao.DaoDemo;
import cn.dao.DaoDemoPrep;
import cn.student.Student;
public class TestDemo {
private IStudentDao stud = new DaoDemo();
//private IStudentDao stud = new DaoDemoPrep();
@Test //
public void testFindAll() {
Student student = new Student();
System.out.println(stud.findAll(student));
}
@Test
public void testSave() {
//模拟封装
Student student = new Student();
student.setAge(18);
student.setId(101);
student.setName("jack");
stud.save(student);
}
@Test //
public void testUpdate() {
Student student = new Student();
student.setAge(18);
student.setId(3);
student.setName("jack");
stud.update(student);
}
@Test
public void testDelete() {
stud.delete(101);
}
@Test
public void testFindById() {
System.out.println(stud.findById(6));
}
@Test
public void testFindId() {
System.out.println(stud.findId(0, 3));
}
}
PrepareStatement(防止sql注入)
就实现接口类改变一下。
package cn.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.IStudentDao.IStudentDao;
import cn.student.Student;
//实现接口
public class DaoDemoPrep implements IStudentDao{
@Override
public List<Student> findAll(Student student) {
//创建一个集合储存学生对象
List<Student> list = new ArrayList<Student>();
//sql语句命令 SELECT id,NAME,age FROM student_jdbc;
String sql = "SELECT id,NAME,age FROM student_jdbc;";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
PreparedStatement state = connection.prepareStatement(sql);
//实现
ResultSet update = state.executeQuery();
while(update.next()) {
Student stud = new Student();
stud.setId(update.getInt("id"));
stud.setName(update.getString("name"));
stud.setAge(update.getInt("age"));
list.add(stud);
}
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public void save(Student student) {
//sql语句命令 INSERT INTO student_jdbc(NAME,age) VALUES ("miemie2",19);
//Student student = new Student();
String sql = "INSERT INTO student_jdbc(id,NAME,age) VALUES (?,?,?)";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
PreparedStatement state = connection.prepareStatement(sql);
//设置参数
state.setInt(1,student.getId());
state.setString(2, student.getName());
state.setInt(3, student.getAge());
//实现
int update = state.executeUpdate();
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void update(Student student) {
//sql语句命令 UPDATE student_jdbc SET age=20 WHERE id=1;
String sql = "UPDATE student_jdbc SET age=?,name=? WHERE id=?";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
PreparedStatement state = connection.prepareStatement(sql);
state.setInt(1,student.getAge());
state.setString(2, student.getName());
state.setInt(3, student.getId());
//实现
int update = state.executeUpdate();
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void delete(int id) {
//sql语句命令 delete from student_jdbc where id=1;
Student student = new Student();
String sql = "delete from student_jdbc where id=?";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
PreparedStatement state = connection.prepareStatement(sql);
state.setInt(1, id);
//实现
int update = state.executeUpdate();
//关闭
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public Student findById(int id) {
//sql语句命令 select id,name,age from student_jdbc where id=1;
Student student = new Student();
String sql = "select name,age from student_jdbc where id=?";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
PreparedStatement state = connection.prepareStatement(sql);
state.setInt(1, id);
//实现
ResultSet rs = state.executeQuery();
while(rs.next()){
student.setId(id);
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
}
//关闭
rs.close();
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
@Override
public List<Student> findId(int index1, int index2) {
//sql语句命令 select id,name,age from student_jdbc LIMIT 0,2;
List<Student> list = new ArrayList<Student>();
//Student student = new Student();
String sql = "SELECT id,NAME,age FROM student_jdbc LIMIT ?,?;";
try {
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//驱动类创建连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc","root","root");
//创建实现方法对象
PreparedStatement state = connection.prepareStatement(sql);
state.setInt(1, index1);
state.setInt(2, index2);
//实现
ResultSet rs = state.executeQuery();
while(rs.next()){
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
list.add(student);
}
//关闭
rs.close();
state.close();
connection.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
TestDemo.java
package cn.test.jdbc;
import static org.junit.Assert.*;
import org.junit.Test;
import cn.IStudentDao.IStudentDao;
import cn.dao.DaoDemo;
import cn.dao.DaoDemoPrep;
import cn.student.Student;
public class TestDemo {
//private IStudentDao stud = new DaoDemo();
private IStudentDao stud = new DaoDemoPrep();
@Test //
public void testFindAll() {
Student student = new Student();
System.out.println(stud.findAll(student));
}
@Test
public void testSave() {
//模拟封装
Student student = new Student();
student.setAge(18);
student.setId(101);
student.setName("jack");
stud.save(student);
}
@Test //
public void testUpdate() {
Student student = new Student();
student.setAge(18);
student.setId(3);
student.setName("jack");
stud.update(student);
}
@Test
public void testDelete() {
stud.delete(101);
}
@Test
public void testFindById() {
System.out.println(stud.findById(6));
}
@Test
public void testFindId() {
System.out.println(stud.findId(0, 3));
}
}
程序中有很多重复的地方,可以写一个工具类,优化代码。