jdbc连接Mysql数据库,增删改查
Student表格信息
//package util,加载驱动,建立数据库的连接,关闭数据库连接
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DbManager {
public static final String url="jdbc:mysql://localhost/jdbctest";
public static final String name="com.mysql.jdbc.Driver";
public static final String user="root";
public static final String password="123456";
public static Connection conn=null;
public static Connection getConnection(){
try {
Class.forName(name); //加载驱动
System.out.println("驱动加载成功!");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.out.println("驱动加载失败!");
e.printStackTrace();
}
try {
conn=DriverManager.getConnection(url,user,password);//建立数据库连接
System.out.println("建立连接成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("建立连接失败!");
e.printStackTrace();
}
return conn;
}
public static void release(Statement stm,Connection conn){
if(stm!=null){
try {
stm.close();
System.out.println("statement 释放成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("statement 释放失败!");
e.printStackTrace();
}
stm=null;
}
if(conn!=null){
try {
conn.close();
System.out.println("断开连接成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("断开连接失败!");
e.printStackTrace();
}
conn=null;
}
}
public static void release(ResultSet rs,Statement stm,Connection conn){
if(rs!=null){
try {
rs.close();
System.out.println("rs释放成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("rs释放失败!");
e.printStackTrace();
}
rs=null;
}
release(stm,conn);
}
}
//bean包中有Student类
package bean;
import java.util.Date;
import java.sql.Timestamp;
public class Student {
private int id;
private String name;
private int grade;
private Date birthday;
private Date f_birthday;
public Student(int id,String name,int grade,Date birthday,Date f_birthday){
this.setId(id);
this.setName(name);
this.setBirthday(birthday);
this.setF_birthday(f_birthday);
this.setGrade(grade);
}
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 getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday2) {
this.birthday = birthday2;
}
public Date getF_birthday() {
return f_birthday;
}
public void setF_birthday(Date f_birthday) {
this.f_birthday = f_birthday;
}
}
//数据库的增、删、改、查操作
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import bean.Student;
import util.DbManager;
public class StudentDAO {
//向数据库中插入数据
public static void insert(Student stu){
Connection conn=DbManager.getConnection();
String sql="insert into student(id,name,grade,birthday,f_birthday) values(?,?,?,?,?)";
try {
PreparedStatement stm=conn.prepareStatement(sql);
stm.setInt(1, stu.getId());
stm.setString(2, stu.getName());
stm.setInt(3, stu.getGrade());
//日期时间类型数据转化为字符串进行插入
SimpleDateFormat sdff=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String strr=sdff.format(stu.getBirthday());
stm.setString(4,strr);
//stm.setTimestamp(4, new java.sql.Timestamp(stu.getBirthday().getTime()));//把java.util.Date转换成java.sql.Timestamp,之后进行插入
//把时间日期转化为字符串进行插入
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String str=sdf.format(stu.getF_birthday());
stm.setString(5, str);
//stm.setDate(5, new java.sql.Date(stu.getF_birthday.getTime()));//把java.util.Date转换成java.sql.Date,之后进行插入
stm.execute();
DbManager.release(stm, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//对数据库内容进行删除操作
public static void delete(Student stu){
Connection conn=DbManager.getConnection();
String sql="delete from student where id=?";
try {
PreparedStatement stm=conn.prepareStatement(sql);
stm.setInt(1, stu.getId());
stm.execute();
System.out.println("删除成功!");
DbManager.release(stm, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//对数据库内容进行更新操作
public static void update(Student stu){
Connection conn=DbManager.getConnection();
//String sql="update student set name=? grade=? birthday=? f_birthday=? where id=?";
String sql="update student set name=?,birthday=? where id=?";
try {
PreparedStatement stm=conn.prepareStatement(sql);
stm.setString(1, stu.getName());
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String str=sdf.format(stu.getBirthday());
stm.setString(2, str);
stm.setInt(3, stu.getId());
stm.executeUpdate();
System.out.println("更新成功!");
DbManager.release(stm, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//查询操作
public static void query(Student stu){
Connection conn=DbManager.getConnection();
String sql="select * from student where id =?";
try {
PreparedStatement stm=conn.prepareStatement(sql);
stm.setInt(1,stu.getId());
ResultSet rs=stm.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3)+" "+rs.getTimestamp(4)+" "+rs.getDate(5));
}
System.out.println("查询成功!");
DbManager.release(rs,stm, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//测试类
package Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import bean.Student;
import util.DbManager;
import dao.StudentDAO;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
Student stu=new Student(2018,"qifengle",87,new Date(),new Date());
//StudentDAO.insert(stu);//插入成功
//StudentDAO.delete(stu);//删除成功
stu.setBirthday(new Date());
StudentDAO.update(stu);
//StudentDAO.query(stu);//查询成功
}
}