java高级用用编程基础练习题2

使用JDBC的步骤，随意创建一个表，创建一个Java项目，编程对该表进行增删改查的操作。

可以只用Eclipse创建如下目录

DB.java

package test;

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

public class DB {
	static Connection con = null;
	static Statement stmt = null;
	static ResultSet rs = null;

	public static void connectToAndQueryDatabase(String driverClass, String database, String username, String password,
			String querySQL) {

		try {
			// 加载JDBC驱动类
			Class.forName(driverClass);
			// 连接数据库
			// DriverManager.setLogWriter(new PrintWriter(System.out));
			con = DriverManager.getConnection(database, username, password);

			// 使用Statement对象执行查询sql语句并获取查询结果存储在ResultSet对象中
			stmt = con.createStatement();
			rs = stmt.executeQuery(querySQL);
			// 根据rs对象获取查询结果的元数据：表的列数			
			ResultSetMetaData rsmd=rs.getMetaData();			
			int columnCount = rsmd.getColumnCount();
			
			//输出元数据：列名
			for(int i=1;i<=columnCount;i++){
				System.out.print(rsmd.getColumnName(i) + "\t");
			}
			System.out.println();
			// 循环输出查询结果
			
			while (rs.next()) {
				for (int i = 1; i <= columnCount; i++) {
					// 输出每列的字段值，列号从1开始编号
					System.out.print(rs.getObject(i) + "\t");
				}
				System.out.println();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//关闭数据库连接
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (con != null && con.isClosed()) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
		public static void connectToAndUpdateDatabase(String driverClass, String database, String username, String password,
				String updateSQL) {
			try {
				// 加载JDBC驱动类
				Class.forName(driverClass);
				// 连接数据库
				// DriverManager.setLogWriter(new PrintWriter(System.out));
				con = DriverManager.getConnection(database, username, password);

				// 使用Statement对象执行更新sql语句，返回值n记录更新了几条记录
				stmt = con.createStatement();
				int n = stmt.executeUpdate(updateSQL);
				System.out.println("已更新了"+n+"条记录");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				//关闭数据库连接
				try {
					if (rs != null) {
						rs.close();
						rs = null;
					}
					if (stmt != null) {
						stmt.close();
						stmt = null;
					}
					if (con != null && con.isClosed()) {
						con.close();
						con = null;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
	}
}

delete.java

package test;


public class delete {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String driverClass="com.mysql.cj.jdbc.Driver";
		//数据库URL字符串
		String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
		//数据库用户名
		String username="root";
		//数据库密码
		String password="hongfengting";
		
		String deleteSQL="DELETE FROM `student`.`student1` WHERE (`no` = '005');";
		DB.connectToAndUpdateDatabase(driverClass, database, username, password, deleteSQL);
		
	}

}

insert.java

package test;

public class insert {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//数据库驱动字符串
				String driverClass="com.mysql.cj.jdbc.Driver";
				//数据库URL字符串
				String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
				//数据库用户名
				String username="root";
				//数据库密码
				String password="hongfengting";
				//更新语句
				String insertSQL="INSERT INTO `student`.`student1` (`no`, `name`, `sex`) VALUES ('005', '小红', '女');";
				DB.connectToAndUpdateDatabase(driverClass, database, username, password, insertSQL);
				
	}

}

select.java

package test;

import java.sql.SQLException;

public class select {
		public static void main(String[] args) throws ClassNotFoundException, SQLException {
			//数据库驱动字符串
			String driverClass="com.mysql.cj.jdbc.Driver";
			//数据库URL字符串
			String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
			//数据库用户名
			String username="root";
			//数据库密码
			String password="hongfengting";
			//查询语句
			String querySQL="select * from student1";
			DB.connectToAndQueryDatabase(driverClass, database, username, password, querySQL);
			
		

		}

	}

update.java

package test;

public class update {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//数据库驱动字符串
		String driverClass="com.mysql.cj.jdbc.Driver";
		//数据库URL字符串
		String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
		//数据库用户名
		String username="root";
		//数据库密码
		String password="hongfengting";
		//更新语句
		String updateSQL="update student1 set SEX='女' where NO='001'";
		DB.connectToAndUpdateDatabase(driverClass, database, username, password, updateSQL);
		//DB.connectToAndQueryDatabase(driverClass, database, username, password, updateSQL);
	}

}