package mysql;

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 TestConnection2 {
	private static  Connection con = null;
	private static ResultSet result=null;
	private static PreparedStatement pre =null;

	public static void main(String[] args) {
		
		
		//1.加载数据库驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("加载失败！");
			e.printStackTrace();
		}
        System.out.println("加载成功！");
	
        //2.连接数据库
        try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db_library", "root", "123");
		} catch (SQLException e) {
			System.out.println("连接数据库失败！");
			e.printStackTrace();
		}
          System.out.println("成功连接数据库！");
          
          //3.处理数据库
          try {
  			 /* pre = con.prepareStatement("create table teacher(id int not null auto_increment,name varchar(20) not null,age int not null,primary key(id))");
  			  int result=pre.executeUpdate();
  			  System.out.println(result);
  		      System.out.println("建表成功！");*/
        	  
        		/*
    			 * insert into table1(name, age) values('李四',22);
    			 */
    			
    			/*String name1="杨旋";
    			int age1=21;
    			pre=con.prepareStatement("insert into teacher(name,age) values(?,?)");
    			pre.setString(1, name1);
    			pre.setInt(2, age1);
    		    pre.executeUpdate();
    		   System.out.println("添加成功！");*/
        	  
        	  
        	  
        	  /*
  			   * update table1 set name='wanger',age=34 where id=1;
  			   */ 
  			/*int id1=1;
  			String name1="何欢";
  			int age1 =22;
  			 pre = con.prepareStatement("update teacher set name=?,age=? where id=?");
  			 pre.setString(1, name1);
  			 pre.setInt(2, age1);
  			 pre.setInt(3, id1);
  		     int result = pre.executeUpdate();
  			 System.out.println(result);
  			  System.out.println("修改成功！");*/
        	  
        	  
        	  /*
        	   * delete from teacher where id=2
        	   */
        	 /* int id1=2;
        	  pre=con.prepareStatement("delete from teacher where id=?");
        	  pre.setInt(1, id1);
        	  pre.executeUpdate();
        	  System.out.println("删除成功！");*/
        	  
        	  /*
        	   * select * from teacher where id>3 
        	   */
        	 
        	  
        	  /*int id1=3;
        	  pre=con.prepareStatement("select * from teacher where id>?");
        	  pre.setInt(1,id1);
        	  result =pre.executeQuery();
        	  while(result.next()){
        		  System.out.println(result.getInt("id"));
        		  System.out.println(result.getString("name"));
        		  System.out.println(result.getInt("age"));
        		  System.out.println("----------------------");
        	  }*/
        	  
        	  /*
        	   * select count(id) from teacher where id>3
        	   */
        	  
        	  int id1=3;
        	  pre=con.prepareStatement("select count(id) from teacher where id>? ");
        	  pre.setInt(1, id1);
        	  result=pre.executeQuery();
        	  if(result.next()){
        		  int count = result.getInt(1);
        		  System.out.println(count);
        	  }
        			    
            } catch (SQLException e) {
          	  System.out.println("执行sql语句出错！");
  			e.printStackTrace();
  			return;
  		}
          
              
          //4.释放数据库的资源
          finally{
        	  try {
        		  if(result!=null)
        		  result.close();
        		  if(pre!=null)
				  pre.close();
        		  if(con!=null)
				  con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
          }
            System.out.println("执行成功！");

	}

}