实验六 通用接口数据库编程

1关：JDBC 查询数据

package step1;
 
import java.sql.*;
 
public class Query {
	public static void queryDB() {
		
		// 声明变量
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
        // "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
        String username = "root";
        String password = "123123";
        // 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
        String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
 
		try {
            /********* Begin *********/
            // 1.加载驱动
        		Class.forName("com.mysql.jdbc.Driver");
 
			// 2.建立数据库连接
               conn = DriverManager.getConnection(url, username, password);
			   
            // 3.定义sql
               String sql =  "select * from s" ;
			// 4.建立statement对象
               stat = conn.createStatement();
 
			// 5.查询数据，输出结果
        rs = stat.executeQuery(sql); 
        System.out.print("sno"+"\t");
        System.out.print("sname"+"\t");
        System.out.print("status"+"\t");
        System.out.println("city");
        while (rs.next()) {
        System.out.print(rs.getString("sno")+"\t");
        System.out.print(rs.getString("sname")+"\t");
        System.out.print(rs.getString("status")+"\t");
        System.out.println(rs.getString("city"));
 
  }
            /********* End *********/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
                if (rs != null) {
					rs.close();
				}
				if (stat != null) {
					stat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
	}
}

第2关：JDBC 插入数据

package step1;

import java.sql.*;

public class Insert {
    public static void insertDB() {

        // 声明变量
        Connection conn = null;
        Statement stat = null;
        int counts = 0; // 用于存储执行SQL后受影响的行数
        // "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
        String username = "root";
        String password = "123123";
        // 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
        String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";

        try {
            /********* Begin *********/
            // 1.加载驱动
               Class.forName("com.mysql.jdbc.Driver");

            // 2.建立数据库连接
               conn = DriverManager.getConnection(url, username, password);

            // 3.建立statement对象
               stat = conn.createStatement();

            // 4.定义SQL
               String sql = "INSERT INTO s VALUES('S6','泰鑫',10,'十堰')";

            // 5.执行SQL
               counts = stat.executeUpdate(sql);

            // 6.处理结果
            if (counts > 0) {
                System.out.println("插入成功!");
                // 如果插入成功，查询表格的SQL语句
                sql = "SELECT * FROM s";
            } else {
                System.out.println("插入失败!");
            }

            /********* End *********/
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stat != null) {
                    stat.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

第3关：JDBC 更新数据

package step1;
 
import java.sql.*;
 
public class Update {
	public static void updateDB() {
		
		// 声明变量
		Connection conn = null;
		Statement stat = null;
        // "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
        String username = "root";
        String password = "123123";
        // 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
        String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
 
		try {
            /********* Begin *********/
			// 1.加载驱动
				Class.forName("com.mysql.jdbc.Driver");
 
			// 2.建立数据库连接
				conn = DriverManager.getConnection(url, username, password);
 
 
			// 3.定义SQL
				String sql= "update s set sname = '东风',status=40,city='十堰'  where sno = 'S5' ";
 
			// 4.建立statement对象
 				stat = conn.createStatement();
 
			// 5.执行SQL
				int counts = stat.executeUpdate(sql);
 
 
       // 6.处理结果
             if(counts>0){
        System.out.println("更新成功!");
        
         }else{
         }
 
 
 
            /********* End *********/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stat != null) {
					stat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

第4关：JDBC 删除数据

 package step1;
 
import java.sql.*;
 
public class Delete {
	public static void deleteDB() {
		
		// 声明变量
		Connection conn = null;
		Statement stat = null;
        // "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
        String username = "root";
        String password = "123123";
        // 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
        String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
 
		try {
            /********* Begin *********/
			// 1.加载驱动
				Class.forName("com.mysql.jdbc.Driver");
 
			// 2.建立数据库连接
				conn = DriverManager.getConnection(url, username, password);
 
			// 3.定义SQL
				String sql = "delete from s where sno='s5'";
 
			// 4.建立statement对象
 				stat = conn.createStatement();
 
			// 5.执行SQL
				int counts = stat.executeUpdate(sql);
 
            // 6.处理结果
        if(counts>0){
        System.out.println("删除成功!");
        
         }else{
         }
 
 
            /********* End *********/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stat != null) {
					stat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

第5关：JDBC 条件查询

package step1;
 
import java.sql.*;
import java.util.Scanner;
 
public class QuerySno {
	public static void queryDB() {
		// 接收供应商编号
		Scanner scanner = new Scanner(System.in);
		String sno = scanner.next();
		// 声明变量
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
        // "root"和"123123"是针对MySQL设置了用户名(root)和密码(123123)
        String username = "root";
        String password = "123123";
        // 127.0.0.1:3306是mysql服务器地址及端口 数据库编码格式设置为utf-8
        String url = "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8";
 
		try {
            /********* Begin *********/
            // 1.加载驱动
				Class.forName("com.mysql.jdbc.Driver");
 
			// 2.建立数据库连接
				conn = DriverManager.getConnection(url, username, password);
 
			// 3.定义sql
				String sql= "select * from s where sno=?";
 
 
			// 4.建立PreparedStatement对象
           		PreparedStatement pstmt = conn.prepareStatement(sql);
			// 5.注册PreparedStatement对象的参数
           		pstmt.setString(1,sno);
 
			// 6.查询数据，输出结果
          rs = pstmt.executeQuery();
 			System.out.print("sno"+"\t");
        System.out.print("sname"+"\t");
        System.out.print("status"+"\t");
        System.out.println("city");
        while (rs.next()) {
        System.out.print(rs.getString("sno")+"\t");
        System.out.print(rs.getString("sname")+"\t");
        System.out.print(rs.getString("status")+"\t");
        System.out.println(rs.getString("city"));
 
  }
 
            /********* End *********/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
                if (rs != null) {
					rs.close();
				}
				if (psmt != null) {
					psmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
	}
}