Java数据库编程技术 第六章习题

1.

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



public class Test {
      public static void main(String[] args) {
		  
    	  Statement statement=null;
    	  ResultSet rs=null;
    	  PreparedStatement pstmt=null;
    	  PreparedStatement 	pstmt2=null;
    	 try {
			
			 Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8";
			 String user="root";
			  String password="123";
			  Connection connection=DriverManager.getConnection(url, user, password);
			 statement = connection.createStatement();
			System.out.println("连接成功！"); 
			
			String sql="CREATE TABLE if not exists  student(id INT(4),name VARCHAR(50));";
			 	pstmt=connection.prepareStatement(sql);
			 	pstmt.executeUpdate();
   	sql="insert into student values(?,?)";
   	pstmt=connection.prepareStatement(sql);
   	pstmt2=connection.prepareStatement(sql);
		pstmt.setInt(1,1);
		pstmt.setString(2, "zhangsan");
		pstmt.executeUpdate();
		pstmt2.setInt(1,2);
		pstmt2.setString(2, "lisi");
		pstmt2.executeUpdate();
		sql="select count(*) from student";
		rs=statement.executeQuery(sql);
		 while(rs.next()) {
		int num=rs.getInt(1);
		System.out.println("表中的记录为"+num+"条");
		 }
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				pstmt.close();
				statement.close();
			} catch (Exception e2) {
				// TODO Auto-generated catch block
				e2.printStackTrace();
			}
		}
	}
}

2

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



public class Test2 {
      public static void main(String[] args) {
		  
    	  Statement statement=null;
    	  ResultSet rs=null;
    	  PreparedStatement pstmt=null;
    	
    	 try {
			
			 Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8";
			 String user="root";
			  String password="123";
			  Connection connection=DriverManager.getConnection(url, user, password);
			 statement = connection.createStatement();
			System.out.println("连接成功"); 
			
			String sql="select * from student";
			 	pstmt=connection.prepareStatement(sql);
			 	rs=pstmt.executeQuery();
			 	System.out.println("id"+"\t"+"name");
			 	while(rs.next()) {
			 		System.out.println(rs.getInt("id")+"\t"+rs.getString("name"));
			 	}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				pstmt.close();
				statement.close();
			} catch (Exception e2) {
				// TODO Auto-generated catch block
				e2.printStackTrace();
			}
		}
	}
}

3

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Test3 {
	public static void main(String[] args) {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement ptmt = null;

		Scanner input = new Scanner(System.in);
		System.out.println("\t宠物主人登录");
		System.out.print("请输入用户名:");
		String name = input.next();
		System.out.print("请输入密码:");
		String pass = input.next();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/epet";
			String user = "root";
			String password = "123";
			try {
				connection = DriverManager.getConnection(url, user, password);
				String sql = "SELECT * FROM  master where name =? and password=?";
				ptmt = connection.prepareStatement(sql);
				ptmt.setString(1, name);
				ptmt.setString(2, pass);
				rs = ptmt.executeQuery();

				if (rs.next()) {
					System.out.println("登录成功");
				} else {
					System.out.println("登录失败");
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			 try {
				rs.close();
				 ptmt.close();
				 connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}

	}
}