使用软件：mysql、eclipse
链接步骤：
1.注册驱动
2.创建一个连接对象
3.写sql语句
4.执行sql语句并返回一个结果或者结果集
5.关闭链接（一般就是connection、statement、setresult）这三个连接对象，关闭顺序一般是（setresult ---> statement --> setresult ）
一、直接连接方法：（这种方法就是讲sql语句和结果所有的步骤写在一起） 不建议使用该方法
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/students";
String user = "root";
String password = "admin";
Connection conn = null;
Statement st = null;
try {
// 1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 创建一个链接对象
conn = DriverManager.getConnection(url,user,password);
// 3. 创建一个sql语句的发送命令对象
String sql = "insert into student values('2001','Tom','20','7000')";
st= conn.createStatement();
// 4. 执行sql语句，拿到查询的结果集对象
st.executeQuery(sql); } catch (Exception e) {
e.printStackTrace();
}finally {
// 5. 关闭链接 ，命令对象 ，结果集
if(st != null) {
try {
st.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二、建立工具类方法，将必要的几步写一个类，使用的时候直接调用（建议使用）
1.注册驱动、创建连接对象、关闭资源 这三部一般可以写一个类，由于写sql语句和执行sql语句的结果不一致，所以可以将其在用到的时候在写
2.一般写工具类都是写成静态方法，以方便调用
//这是工具类： import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement; public class JdbcUtils {
private static String driverName = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/student_achievement_system";
private static String user = "root";
private static String password = "admin";
/**
* 链接数据库
*/
static {
try {
Class.forName(JdbcUtils.driverName);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取链接对象connection
* @return
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection(JdbcUtils.url, JdbcUtils.user, JdbcUtils.password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭资源
* @param conn
* @param st
* @param rs
*/
public static void close(Connection conn,Statement st,ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(st != null) {
try {
st.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
//这是对数据库的基本操作类
// 增加、删除、更新、查找一条、查找所有的方法
public class StudentsDaoImpl implements IStudentsDao {
//增加
@Override
public int save(Students student) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into students values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, student.getStudentId());
ps.setString(2, student.getStudentName());
ps.setString(3, student.getSex());
ps.setString(4, student.getPhoneNo());
ps.setString(5, student.getAddress());
ps.setDate(6, (Date) student.getBirthday());
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, null);
}
return 0;
}
//删除
@Override
public int delete(int studentId) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "delete from students where studentId=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, studentId);
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, null);
}
return 0;
}
//更新
@Override
public int update(int studentId, Students student) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "update students set studentName=?,sex=?,phoneNo=?,address=?,birthday=? where studentId=?";
ps = conn.prepareStatement(sql);
ps.setString(1, student.getStudentName());
ps.setString(2, student.getSex());
ps.setString(3, student.getPhoneNo());
ps.setString(4, student.getAddress());
ps.setDate(5, ((Date) student.getBirthday()));
ps.setInt(6, studentId);
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, null);
}
return 0;
}
//查找一条数据
@Override
public Students getByStudentId(int studentId) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from students where studentId=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, studentId);
rs = ps.executeQuery();
if(rs.next()) {
Students student = new Students();
student.setStudentId(rs.getInt("studentId"));
student.setStudentName(rs.getString("studentName"));
student.setSex(rs.getString("sex"));
student.setPhoneNo(rs.getString("phoneNo"));
student.setAddress(rs.getString("address"));
student.setBirthday(rs.getDate("birthday"));
return student;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, rs);
}
return null;
}
//查找所有数据
@Override
public List<Students> getAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from students";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
List<Students> studentsList = new ArrayList<>();
while(rs.next()) {
Students student = new Students();
student.setStudentId(rs.getInt("studentId"));
student.setStudentName(rs.getString("studentName"));
student.setSex(rs.getString("sex"));
student.setPhoneNo(rs.getString("phoneNo"));
student.setAddress(rs.getString("address"));
student.setBirthday(rs.getDate("birthday"));
studentsList.add(student);
}
return studentsList;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, rs);
}
return null;
}
}