JDBC(一)
基础访问数据库
步骤一
- 导入mysql-connector-java-5.1.45-bin.jar
package com.lanou.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCUtil {
//1.定义连接数据库所需要的数据值
//驱动路径
private static final String DRIVER = "com.mysql.jdbc.Driver";
//数据库链接school是在MySQL中创建的数据库
private static final String URL = "jdbc:mysql://localhost:3306/school?characterEncoding=utf-8";
//账户
private static final String USERNAME = "root";
//密码
private static final String PASSWORD = "123456";
//2.加载驱动类
static{
//静态代码块,静态区域中的代码只会加载一次!
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//3.获取数据库连接对象
public static Connection getConnection() {
Connection connection = null;
try {
connection=DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
}
步骤二
根据数据库中的字段创建一个学生类
package com.lanou.bean;
import java.util.Date;
public class Student {
private int id;
private String name;
private String gender;
private int birthday;
private String deparetment;
private String adress;
public Student() {
}
public Student(int id, String name, String gender, int birthday, String deparetment, String adress) {
super();
this.id = id;
this.name = name;
this.gender = gender;
this.birthday = birthday;
this.deparetment = deparetment;
this.adress = adress;
}
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 String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getBirthday() {
return birthday;
}
public void setBirthday(int birthday) {
this.birthday = birthday;
}
public String getDeparetment() {
return deparetment;
}
public void setDeparetment(String deparetment) {
this.deparetment = deparetment;
}
public String getAdress() {
return adress;
}
public void setAdress(String adress) {
this.adress = adress;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", gender=" + gender + ", birthday=" + birthday
+ ", deparetment=" + deparetment + ", adress=" + adress + "]";
}
}
步骤三
创建dao包在dao包中创建一个接口并在这个包中创建这个包的实现类
package com.dao;
import java.util.ArrayList;
import com.lanou.bean.Student;
public interface InterfaceStudent {
//1.未封装数据模型
//
int insertData();
//2.更新
int updateData();
//3.删除
int deleteData();
//4.查询
void selectAll();
//插入
int insertStudent(Student stu);
//更新
int updateStudent(int id, String name, String gender, int birthday, String department, String adress);
//3.删除
int deleteStudentByNumber(int id);
//4.查询全部数据 每一行(一条数据)--->封装成模型对象---->放入数组中
ArrayList<Student> selectAllStudent();
//5.查询单个学生
Student selectOneStudentByNumber(int id);
}
创建接口的实现类并实现其接口的方法
查询时query
增删改时update
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import com.lanou.bean.Student;
import com.lanou.util.JDBCUtil;
public class ImpStudent implements InterfaceStudent {
@Override
public int insertData() {
//1.定义row 接受数据库中受影响(插入成功)的行数
int row = 0;
//定义sql语句
String sql = "insert into student(id,name,gender,birthday,department,adress) values(5,'小帅哥','男',1998,'英语系','福建省厦门市')";
//3.获取数据库连接对象
Connection connection = JDBCUtil.getConnection();
//4.通过连接对象 获取sql命令的对象
try {
PreparedStatement prepareStatement = connection.prepareStatement(sql);
//5.开始执行
row = prepareStatement.executeUpdate();
//6.关闭数据库
prepareStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
@Override
public int updateData() {
int row = 0;
String sql="update student set gender = '女' where id = 6";
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
row = statement.executeUpdate();
connection.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
@Override
public int deleteData() {
int row = 0;
String sql="delete from student where id = 5";
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
row = statement.executeUpdate();
//关闭连接
connection.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
@Override
public void selectAll() {
String sql = "select * from student";
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
Date birthday = resultSet.getDate("birthday");
String department = resultSet.getString("department");
String adress = resultSet.getString("adress");
System.out.println(id+" " + name + " " + gender + " " + birthday + " " + department + " " +adress);
}
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public int insertStudent(Student stu) {
int row = 0;
String sql = "insert into student values(?, ?, ?, ?, ?, ?)";
//? 在此是占位符,还可以避免sql注入 是sql语句更加安全
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, stu.getId());
statement.setString(2, stu.getName());
statement.setString(3, stu.getGender());
statement.setInt(4, stu.getBirthday());
// statement.setDate(4, stu.getBirthday());
statement.setString(5, stu.getDeparetment());
statement.setString(6, stu.getAdress());
row = statement.executeUpdate();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
@Override
public int updateStudent(int id, String name, String gender, int birthday, String department,
String adress) {
int row = 0;
String sql = "update student set name=?,gender=?,birthday=?,department=?,adress=? where id=?";
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, name);
statement.setString(2, gender);
statement.setInt(3, birthday);
statement.setString(4, department);
statement.setString(5, adress);
statement.setInt(6, id);
row= statement.executeUpdate();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
@Override
public int deleteStudentByNumber(int id) {
int row = 0;
String sql="delete from student where id=?";
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, id);
row=statement.executeUpdate();
statement.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return row;
}
@Override
public ArrayList<Student> selectAllStudent() {
ArrayList<Student> studentList = new ArrayList<>();
String sql = "select * from student";
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
int birthday = resultSet.getInt("birthday");
String department = resultSet.getString("department");
String adress = resultSet.getString("adress");
Student stu = new Student(id, name, gender, birthday, department, adress);
studentList.add(stu);
}
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return studentList;
}
@Override
public Student selectOneStudentByNumber(int id) {
Student student = new Student();
String sql = "select * from student where id=?";
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, id);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setGender(resultSet.getString("gender"));
student.setBirthday(resultSet.getInt("birthday"));
student.setDeparetment(resultSet.getString("department"));
student.setAdress(resultSet.getString("adress"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return student;
}
}
步骤四:测试类
package com.main
import java.util.ArrayList;
import java.util.Date;
import com.lanou.bean.Student;
import com.lanou.dao.ImpStudent;
import com.lanou.dao.InterfaceStudent;
import com.lanou.prodao.ProImpStu;
import com.lanou.prodao.ProInterfaceStu;
import com.mysql.jdbc.UpdatableResultSet;
public class Main {
public static void main(String[] args) {
InterfaceStudent manage = new ImpStudent();
nt num = manage.insertData();
System.out.println(num > 0 ? "插入成功":"插入失败");
num = manage.updateData();
System.out.println(num > 0 ? "更新成功":"更新失败");
num = manage.deleteData();
System.out.println(num > 0 ? "删除成功":"删除失败");
manage.selectAll();
ArrayList<Student> studentArray = manage.selectAllStudent();
for (Student student : studentArray) {
System.out.println(student);
}
Student stu = manage.selectOneStudentByNumber(6);
System.out.println("单条查询:" + stu);
//修改
int row = manage.updateStudent(6, "王二狗子", "男", 1998, "英语系", "福建省厦门市");
System.out.println(row > 0 ? "修改成功" : "修改失败");
//插入
Student student =new Student(7, "品如", "女", 1988,"艺术系", "河南信息");
System.out.println(row > 0 ? "插入成功" : "插入失败");
}
}