Java作业四（下）

题目

以MySQL为例,数据库名为MyDB，数据表为Customer(ID,Name,Age, Gender,Address)实现基本的增、删、改、查功能，用statement和preparedstatement来实现(注意事务处理)

代码

首先要在当前项目中导入JDBC包，在我的实例中数据库名称为experiment。

package homework;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Work_4 {
	static String DBDRIVER = "com.mysql.cj.jdbc.Driver" ;
	static String DBURL = "jdbc:mysql://数据库地址:数据库端口/experiment? usessl=false" ;//服务器ip地址
	static String DBUSER = "experiment" ;
	static String DBPASS = "密码" ;
	String id= "1001";
	String name= "李四";
	String gender = "男";
	String address = "北京市朝阳区";
	int age = 18;
	Connection con;
	public void add(){
		try {
			String add = "insert into Customer(ID,Name,Age,Gender,Address) values(?,?,?,?,?)";
			 PreparedStatement sql = con.prepareStatement(add);
			 sql.setString(1, id);
			 sql.setString(2, name);
			 sql.setLong(3, age);
			 sql.setString(4, gender);
			 sql.setString(5, address);
			 if(sql.executeUpdate() > 0)
    			 System.out.println("添加成功");
			sql.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("失败");
			e.printStackTrace();
		}
	}
	public void find() {
		String find = "select * from Customer where ID = '"+id+"'";
		Statement sql;
		ResultSet rs;
		try {
			sql = con.createStatement();
			 rs = sql.executeQuery(find);
			 System.out.println("查找:");
			 while(rs.next()) {
				 for(int i=1;i<=5;i++)
				System.out.print(rs.getString(i)+"\t");
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
	}
	public void update() {
		try {
			age = 20;
			address = "长春市朝阳区";
			String update = "update Customer set Age = ?,Address = ? where id = ?";
			 PreparedStatement sql = con.prepareStatement(update);
			 sql.setLong(1, age);
			 sql.setString(2, address);
			 sql.setString(3, id);
			 if(sql.executeUpdate() > 0)
    			 System.out.println("\n修改后:");
			 String find = "select * from Customer where ID = '"+id+"'";
			 ResultSet rs = sql.executeQuery(find);
			 while(rs.next()) {
				 for(int i=1;i<=5;i++)
				System.out.print(rs.getString(i)+"\t");
			 }
			sql.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("失败");
			e.printStackTrace();
		}
	}
	public void delete() {
		String delete = "delete from Customer where ID = '"+id+"'";
		try {
			Statement sql = con.createStatement();
			if(sql.executeUpdate(delete)>0) {
				System.out.println("\n删除成功");
			}
			sql.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		Work_4 test = new Work_4();
		try {
			Class.forName(DBDRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			test.con = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		test.add();
		test.find();
		test.update();
		test.delete();
	}
}