MyEclipse 下MySQL的数据库增删改查操作详解_myeclipse数据库增删改查封装

MyEclipse 下的mySQL

建立数据库连接 并且实现简单的数据库增查改删，内附实例

2017 版myEclipse
8.0.16 mySQL-connector
作业要求如下：

增删改查独立为方法，在main函数里调用，为了代码的美观，我都把一些固定的变量写在外面啦～
数据库：database8
数据表：newProduct
因为我的mySQL版本比较新，“com.mysql.jdbc.Driver"变成"com.mysql.cj.jdbc.Driver”

private static final String DRIVER ="com.mysql.cj.jdbc.Driver"; //更新
	private static final String URL = "jdbc:mysql://localhost:3306/database8";
	private static final String ACCOUNT = "root";
	private static final String PSW = "12345";
1
2
3
4

//查询数据表全部数据，如下

	public static void queryAll() {
		try{
		Class.forName(DRIVER); //载入JDBC驱动
		Connection conn = DriverManager.getConnection(URL, ACCOUNT,PSW);//建立数据库连接
		Statement stmt =  conn.createStatement();//创建statement对象
		
		String sql = "SELECT * FROM database8.newProduct";//查询语句
		ResultSet rs = stmt.executeQuery(sql);//创建数据对象，执行sql
		System.out.println("id"+"\t"+"pName"+"\t"+"origin"+"\t"+"price"+"\t"+"inventory");
		while(rs.next()){
			System.out.print(rs.getInt("id")+"\t");
			System.out.print(rs.getString("pName")+"\t");
			System.out.print(rs.getString("origin")+"\t");
			System.out.print(rs.getFloat("price")+"\t");
			System.out.print(rs.getInt("inventory")+"\t");
			System.out.println();
		}
		rs.close();
		stmt.close();
		conn.close();
		} catch (ClassNotFoundException ex){
			ex.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

//查询大于指定价格的所有产品,如下

	public static void printAbovePrice(float price){
		try{
			Class.forName(DRIVER); //载入JDBC驱动
			Connection conn = DriverManager.getConnection(URL, ACCOUNT,PSW);//建立数据库连接
			Statement stmt =  conn.createStatement();//创建statement对象
			
			String sql1 = "SELECT * FROM database8.newProduct WHERE price>";//查询语句
			Float Price = new Float(price);
			String sql = sql1 +Price.toString();
			ResultSet rs = stmt.executeQuery(sql);//创建数据对象，执行sql
			System.out.println("id"+"\t"+"pName"+"\t"+"origin"+"\t"+"price"+"\t"+"inventory");
			while(rs.next()){
				System.out.print(rs.getInt("id")+"\t");
				System.out.print(rs.getString("pName")+"\t");
				System.out.print(rs.getString("origin")+"\t");
				System.out.print(rs.getFloat("price")+"\t");
				System.out.print(rs.getInt("inventory")+"\t");
				System.out.println();
			}
			rs.close();
			stmt.close();
			conn.close();
			} catch (ClassNotFoundException ex){
				ex.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

//增加指定数据，如下

public static void add(int id, String pName, String origin, float price,int invent) {
		try{
			Class.forName(DRIVER); //载入JDBC驱动
			Connection conn = DriverManager.getConnection(URL, ACCOUNT,PSW);//建立数据库连接
			Statement stmt =  conn.createStatement();//创建statement对象
			
			String sql1 = "INSERT INTO newProduct VALUES(";
			Integer Id = new Integer(id);
			Integer Invent = new Integer(invent);
			Float Price = new Float(price);
			String sql = sql1 + Id.toString()+",'"+pName+"','"+origin+"',"+Price.toString()+","+Invent.toString()+")";
			stmt.executeUpdate(sql);//创建数据对象,执行sql
			System.out.println("Success in insert!" );
			stmt.close();
			conn.close();
			} catch (ClassNotFoundException ex){
				ex.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

删除指定id，如下

public static void delete( int id) {
		try{
			Class.forName(DRIVER); //载入JDBC驱动
			Connection conn = DriverManager.getConnection(URL, ACCOUNT,PSW);//建立数据库连接
			Statement stmt =  conn.createStatement();//创建statement对象
			String sql1 = "DELETE FROM newProduct WHERE id=";
			
			Integer Id = new Integer(id);
			String sql = sql1+Id.toString() ;//删除id语句
			stmt.executeUpdate(sql);//创建数据对象,执行sql
			System.out.println("Success in delete!" );
			stmt.close();
			conn.close();
			} catch (ClassNotFoundException ex){
				ex.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

//修改指定数据的指定库存，如下

public static void update(int id, int invent) {
		try{
			Class.forName(DRIVER); //载入JDBC驱动
			Connection conn = DriverManager.getConnection(URL, ACCOUNT,PSW);//建立数据库连接
			Statement stmt =  conn.createStatement();//创建statement对象
			
			String sql1 = "UPDATE newProduct SET inventory =inventory-";
			String sql2 = " WHERE id=";
			Integer Id = new Integer(id);
			Integer Invent = new Integer(invent);
			String sql = sql1 +Invent.toString()+ sql2+ Id.toString();//更新id=4语句
			stmt.executeUpdate(sql);//创建数据对象,执行sql
			System.out.println("Success in update!" );
			stmt.close();
			conn.close();
			} catch (ClassNotFoundException ex){
				ex.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

最后的最后，当然是一个main函数调用啦～

public static void main (String args[]){
		Product p = new Product();
		//p.delete(5);
		//p.add(6,"iTouch","China",399,5000);
		//p.printAbovePrice(4000);
		p.update(2, 500);
		//p.queryAll();
	}
1
2
3
4
5
6
7
8

终端截图，如下

我是利用字符串的拼接完成mySQL的操作滴，在拼接的时候需要注意：

1. 增加完整数据时，正常插入应该是类似这样：INSERT INTO product VALUES(2,‘三星显示器’，‘电脑组件’，‘杭州’，870)； 所以在拼接时不要忘记逗号左右两边的单引号

我也有一个问题：
我的每个方法里面都重新建立连接，这有必要吗？有没有什么可以省略的部分？欢迎大家指导哦～