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);//查询成功

}
}