jdbc --例子7

package cn.kitty.o1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Test {
    
  public static void main(String[] args){
    Connection conn=null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    //2.
    try {
        conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/epet?useUnicode=true&characterEncoding=utf-8","root","");
        System.out.println("连接成功");
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        //3
        if (null!=conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    
}
}

package cn.kitty.o1;


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

//Statement
public class Test02 {
  public static void main(String[] args) {
    Connection conn=null;
    Statement stmt=null;
    String name="kitty";
    int health=100;
    int love =10;
    String strain="可爱的小公举3";
    //1
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    //2.
    try {
        conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/epet","root","");
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
    //3.
    try {
        stmt=conn.createStatement();
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
    StringBuffer sbSql=new StringBuffer(
            "insert into dog(name,health,love,strain)values('");
                    sbSql.append(name+"',");
                    sbSql.append(health+",");
                    sbSql.append(love+",'");
                    sbSql.append(strain+"')");
                    try {
                        stmt.execute(sbSql.toString());
                        System.out.println("添加数据成功");
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }finally{
                                try {
                                    if (null !=stmt) {
                                    stmt.close();
                                    }
                                } catch (SQLException e) {
                                    // TODO Auto-generated catch block
                                    e.printStackTrace();
                                }
                            
                                try {
                                    if(null!=conn){
                                    conn.close();
                                    }
                                } catch (SQLException e) {
                                    // TODO Auto-generated catch block
                                    e.printStackTrace();
                                
                            }
                    }
    
}
}

package cn.kitty.o1;

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

//更新宠物Statement
public class Test03 {
    public static void main(String[] args) {
        Connection conn=null;
        Statement stmt=null;
        
        //1.加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //2.建立连接
        try {
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/epet","root","");
        
            stmt=conn.createStatement();
            stmt.executeUpdate("update dog set health=1000,love=1500 where id=1");
            System.out.println("成功更新狗狗信息");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }finally{
                                   try {
                                        if (null !=stmt) {
                                        stmt.close();
                                        }
                                    } catch (SQLException e) {
                                        // TODO Auto-generated catch block
                                        e.printStackTrace();
                                    }
                                
                                    try {
                                        if(null!=conn){
                                        conn.close();
                                        }
                                    } catch (SQLException e) {
                                        // TODO Auto-generated catch block
                                        e.printStackTrace();
                                    
                                }
                        }
        
    }

}
  

package cn.kitty.o1;

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

//查询所有宠物Statement和ResultSet
public class Test04 {
public static void main(String[] args) {
    Connection conn=null;
    Statement stmt=null;
    ResultSet rs=null;
    //1
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //2.
        try {
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/epet","root","");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        //3.查询并输入狗狗信息
        try {
            stmt=conn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            rs=stmt.executeQuery("select id,name,health,love,strain from dog");
            System.out.println("狗狗信息列表");
            System.out.println("编号\t姓名\t健康值\t亲密度\t品种");
            while(rs.next()){
                System.out.print(rs.getInt(1)+"\t");
                System.out.print(rs.getString(2)+"\t");
                System.out.print(rs.getInt("health")+"\t");
                System.out.print(rs.getInt("love")+"\t");
                System.out.println(rs.getString("strain"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(null!=rs){
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(null!=stmt){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(null!=conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        
}
}

package cn.kitty.o1;

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

//查询并输出宠物主人信息
public class Test05 {
   public static void main(String[] args) {
       Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        //1
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            //2.
            try {
                conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/epet","root","");
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            //3.
            try {
                stmt=conn.createStatement();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            try {
                rs=stmt.executeQuery("select id ,name,money from master");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            System.out.println("主人信息列表");
            System.out.println("编号\t姓名\t元宝数");
            try {
                while(rs.next()){
                    System.out.print(rs.getInt("id")+"\t");
                    System.out.print(rs.getString("name")+"\t");
                    System.out.println(rs.getInt("money"));
                    
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                if(null!=rs){
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                if(null!=stmt){
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                if(null!=conn){
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
   }
}

package cn.kitty.o1;

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

//
public class Test06 {
public static void main(String[] args) {
     Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        //根据控制台提示输入用户账号和密码
        Scanner input =new Scanner(System.in);
        System.out.println("宠物主人登录");
        System.out.println("请输入姓名：");
        String name =input.next();
        System.out.println("请输入密码：");
        String password=input.next();
        //1.加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //2.
        try {
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/epet?useUnicode=true&characterEncoding=utf-8","root","");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        //判断宠物主人登录成功
        try {
            stmt=conn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String sql="select * from master where name='"+name+"' and password='"+password+"'";
        System.out.println(sql);
        try {
            rs=stmt.executeQuery(sql);
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            if(rs.next())
                System.out.println("登录成功，欢迎你");
            else
                System.out.println("登录失败，请重新输入！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(null!=rs){
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(null!=stmt){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(null!=conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        
     }
}

package cn.kitty.o1;

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



//preparedStatment 更新多条狗狗信息
public class Test07 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        //1.加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //2.建立连接
        try {
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/epet?useUnicode=true&characterEncoding=utf-8","root","");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        //3.更新狗狗数据到数据库
        String sql="update dog set health=?,love=? where id=?";
        try {
            pstmt=conn.prepareStatement(sql);
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            pstmt.setInt(1, 90);
            pstmt.setInt(2, 90);
            pstmt.setInt(3, 1);
            pstmt.executeUpdate();
            pstmt.setInt(1, 90);
            pstmt.setInt(2, 90);
            pstmt.setInt(3, 2);
            pstmt.executeUpdate();
            System.out.println("更新狗狗信息成功");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }finally{
            
        
            if(null!=pstmt){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(null!=conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            
        
        }

}}