JDBC之Java连接mysql实现增删改查

使用软件：mysql、eclipse

链接步骤：

1.注册驱动 

2.创建一个连接对象

3.写sql语句

4.执行sql语句并返回一个结果或者结果集

5.关闭链接（一般就是connection、statement、setresult）这三个连接对象，关闭顺序一般是（setresult    --->  statement  -->  setresult  ）

一、直接连接方法：（这种方法就是讲sql语句和结果所有的步骤写在一起） 不建议使用该方法

 1 public static void main(string[] args) {
 2         string url = "jdbc:mysql://localhost:3306/students";
 3         string user = "root";
 4         string password = "admin";
 5         connection conn = null;
 6         statement st = null;
 7         
 8         try {
 9             // 1. 注册驱动
10             class.forname("com.mysql.jdbc.driver");
11             // 2. 创建一个链接对象
12             conn = drivermanager.getconnection(url,user,password);
13             // 3. 创建一个sql语句的发送命令对象
14             string sql = "insert into student values('2001','tom','20','7000')";        
15             st= conn.createstatement();
16             // 4. 执行sql语句，拿到查询的结果集对象
17             st.executequery(sql);20         } catch (exception e) {
21             e.printstacktrace();
22         }finally {
23             // 5. 关闭链接 ，命令对象 ，结果集
24             if(st != null) {
25                 try {
26                     st.close();
27                 } catch (exception e) {
28                     e.printstacktrace();
29                 }
30             }
31             if(conn != null) {
32                 try {
33                     conn.close();
34                 } catch (exception e) {
35                     e.printstacktrace();
36                 }
37             }
38         }

二、建立工具类方法，将必要的几步写一个类，使用的时候直接调用（建议使用）

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;
	}
}