18°

JAVA--高级基础开发JDBC

//封装JDBC工具包
driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/ab_wzy?serverTimezone=UTC&character=utf8
user=root
password=105104
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
    private  static String  url;
    private  static String  user;
    private  static String password;
    private  static Properties P;
    static {
        P=new Properties();
        //读取属性文件
        InputStream  input=Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
        //加载P对象
        try{
            P.load(input);
        }catch(IOException  ce){
            ce.printStackTrace();
        }
    //根据键获取值
   url=P.getProperty("url");
   user=P.getProperty("user");
   password=P.getProperty("password");
}

//获取数据库连接对象
public  static Connection  getConnection()throws SQLException {
    Connection con= DriverManager.getConnection(url,user,password);
    return con;
}
//关闭数据库连接对象之insert  delete update的操作
public static  void  close(Connection  con, Statement state)throws SQLException{
    con.close();;
    state.close();
}
//关闭数据库连接的对象之 select 查找查询的操作
public static  void close(Connection  con, Statement  state, ResultSet  set)throws SQLException{
     set.close();
     state.close();
     con.close();
}
//关闭获取数据库连接对象
public  static  void  close(Connection  con)throws SQLException{
    con.close();
}
// 关闭执行Statement执行SQL 语句的对象
public static  void close(Statement  state)throws SQLException{
    state.close();
}
//关闭结果集对象ResultSet对象
public static  void close(ResultSet  set)throws SQLException{
    set.close();
}

} //案例:用户登录 public class jdbc_demo1 { public static void main(String[] args) { Scanner input=new Scanner(System.in); System.out.println("请输入用户名"); String name=input.nextLine(); System.out.println("请输入密码"); String password=input.nextLine(); Login(name,password); } // 用户登录的方法 public static void Login(String name,String password){ Connection con=null; Statement state=null; ResultSet set=null; //通过工具类来获取连接对象 try{ con= JdbcUtils.getConnection(); //获取Statement执行SQL语句的对象 state=con.createStatement(); //准备登录的SQL语句。登录就是从数据库表中查询数据 String sql="select * from user where name='"+name+"'and password='"+password+"'"; // 执行SQL语句 set=state.executeQuery(sql); if(set.next()){ System.out.println("登录成功,欢迎"+name); }else{ System.out.println("登录失败"); } }catch(SQLException ce){ ce.printStackTrace(); }finally { try{ JdbcUtils.close(con,state,set); }catch(SQLException ce){ ce.printStackTrace(); }

    }
}

} //使用reparedStatement接口 //防止SQL注入的问题 public class jdbc_demo2 { public static void main(String[] args) { Scanner input=new Scanner(System.in); System.out.println("请输入用户名"); String name=input.nextLine(); System.out.println("请输入密码"); String password=input.nextLine(); Login(name,password); } //定义用户登录的方法 public static void Login(String name,String password){ Connection con=null; PreparedStatement statement=null; ResultSet set=null; try{ //根据工具包获取连接对象 con= JdbcUtils.getConnection(); //准备登录的SQL语句 String sql="select * from user where name=? and password=?"; //获取PreparedStatement执行的SQL语句的对象 statement=con.prepareStatement(sql); //给占位符赋值 statement.setString(1,name); statement.setString(2,password); //执行SQL语句 set=statement.executeQuery(); if(set.next()){ System.out.println("登录成功,欢迎"+name); }else{ System.out.println("登录失败"); } }catch(SQLException ce){ ce.printStackTrace(); }finally { try{ JdbcUtils.close(con,statement,set); }catch(SQLException ce){ ce.printStackTrace(); }

    }
}

} //表与类之间的关系 public class jdbc_demo3 { public static void main(String[] args) { List<Student>list=finAll(); for(Student ss:list){ System.out.println(ss); } } //查询表中所有的信息 public static List<Student> finAll(){ Connection con=null; PreparedStatement state=null; ResultSet set=null; List<Student>list=new ArrayList<>(); try{ //获取连接对象 con= JdbcUtils.getConnection(); //准备SQL 语句 String sql="select * from student"; //获取PrepareStatement对象 state=con.prepareStatement(sql); set=state.executeQuery(); //先声明的对象 Student student=null; while(set.next()){ //每次创建的对象,都会把上次对象给覆盖掉,始终保持只占用一块内存空间 student=new Student(); student.setId(set.getInt("id")); student.setName(set.getString("name")); student.setGender(set.getString("gender")); student.setBirthday(set.getDate("birthday")); list.add(student); } }catch(SQLException ce){ ce.printStackTrace(); } return list; } }

//JDBC 事物的处理案例 public class jdbc_demo5 { public static void main(String[] args) { Connection con=null; PreparedStatement state=null; try{ //获取Connection对象 con= JdbcUtils.getConnection(); //开启事物 默认为fasle con.setAutoCommit(false); //给李文杰扣钱 String sql="update common set money=money-? where id=?"; state=con.prepareStatement(sql); //给占位符赋值 state.setString(1,"500"); state.setInt(2,1); //执行SQL语句 state.executeUpdate(); //给郭朝旭加钱

        String  sql2="update common set money=money+? where id=?";
        state=con.prepareStatement(sql2);
        //给占位符赋值
        state.setString(1,"500");
        state.setInt(2,2);
        //执行SQL语句
       int i=state.executeUpdate();
       if(i&gt;0){
           System.out.println("转账成功");
       }
        //执行完毕,关闭事物
        con.commit();

    }catch(SQLException  ce){
        ce.printStackTrace();
        //如果发生异常,就回滚 rollback
        try{
            con.rollback();
        }catch(SQLException  ces){
            ces.printStackTrace();
        }
        System.out.println("转账失败");
    }finally {
        try{
            JdbcUtils.close(con,state);
        }catch(SQLException  ce){
            ce.printStackTrace();
        }

    }
}

} //JAVABean Student public class Student implements Serializable { private static final long serialVersionUID = 8394954963273410188L; private int id; private String name; private String gender; private Date birthday; public Student(){

}
public Student(int id, String name, String gender, Date birthday) {
    this.id = id;
    this.name = name;
    this.gender = gender;
    this.birthday = birthday;
}

public static long getSerialVersionUID() {
    return serialVersionUID;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getGender() {
    return gender;
}

public void setGender(String gender) {
    this.gender = gender;
}

public Date getBirthday() {
    return birthday;
}

public void setBirthday(Date birthday) {
    this.birthday = birthday;
}

@Override
public String toString() {
    return "Student{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", gender='" + gender + '\'' +
            ", birthday='" + birthday + '\'' +
            '}';
}

}

 

本文由【李文杰-yaya】发布于开源中国,原文链接:https://my.oschina.net/u/4244602/blog/3136610

全部评论: 0

    我有话说: