SQL的应用

package com.phone.week6.day1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Tab1509Dao {

public static void main(String[] args) {
    try {
        //1.加载驱动程序到内存中
        Class.forName("org.sqlite.JDBC");
        //2.获得连接
        Connection conn = DriverManager.getConnection("jdbc:sqlite:D:test.db");
        //3.创建执行SQL语句的对象
        String sql = "update tab1509 set name=? where id=5";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "Andy");
        //4.执行SQL语句
        int rs = ps.executeUpdate(); //执行增删改
        if(rs>0){
            System.out.println("操作成功");
        }else{
            System.out.println("操作失败");
        }
        //6.释放资源

        ps.close();
        conn.close();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

private static void delete() {
    try {
        //1.加载驱动程序到内存中
        Class.forName("org.sqlite.JDBC");
        //2.获得连接
        Connection conn = DriverManager.getConnection("jdbc:sqlite:D:\\150720\\week6\\romsemary.db");
        //3.创建执行SQL语句的对象
        String sql = "delete from tab1509 where id=4";
        PreparedStatement ps = conn.prepareStatement(sql);

        //4.执行SQL语句
        int rs = ps.executeUpdate(); //执行增删改
        if(rs>0){
            System.out.println("操作成功");
        }else{
            System.out.println("操作失败");
        }
        //6.释放资源

        ps.close();
        conn.close();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

private static void add() {
    try {
        //1.加载驱动程序到内存中
        Class.forName("org.sqlite.JDBC");
        //2.获得连接
        Connection conn = DriverManager.getConnection("jdbc:sqlite:D:\\150720\\week6\\romsemary.db");
        //3.创建执行SQL语句的对象
        String sql = "insert into tab1509 (name,age) values(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        //处理问题
        ps.setString(1, "sushaobin");
        ps.setInt(2,25);
        //4.执行SQL语句
        int rs = ps.executeUpdate(); //执行增删改
        if(rs>0){
            System.out.println("操作成功");
        }else{
            System.out.println("操作失败");
        }
        //6.释放资源

        ps.close();
        conn.close();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

//查询数据
private static void select() {
    List<Tab1509> list = new ArrayList<>();
    try {
        //1.加载驱动程序到内存中
        Class.forName("org.sqlite.JDBC");
        //2.获得连接
        Connection conn = DriverManager.getConnection("jdbc:sqlite:D:test.db");
        //3.创建执行SQL语句的对象
        String sql = "select * from tab1509";
        PreparedStatement ps = conn.prepareStatement(sql);
        //4.执行SQL语句,并返回一个结果集
        ResultSet rs = ps.executeQuery();
        //5.处理结果集
        while(rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            //创建一个对象来封装这条数据
            Tab1509 tb = new Tab1509(id, name, age);
            list.add(tb); //添加到集合里
        }
        //6.释放资源
        rs.close();
        ps.close();
        conn.close();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }


    for (Tab1509 tb : list) {
        System.out.println(tb.getId()+"\t"+tb.getName()+"\t"+tb.getAge());
    }
}

}

package com.phone.week6.day1;

public class Tab1509 {

private int id;
private String name;
private int age;
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 int getAge() {
    return age;
}
public void setAge(int age) {
    this.age = age;
}
public Tab1509(int id, String name, int age) {
    super();
    this.id = id;
    this.name = name;
    this.age = age;
}

}