(jdbc)使用java代码对sql进行操作

使用java代码对sql进行操作  需要的外包为：sqlitejdbc-0.5.4.jar         

package com.mobiletrain.jdbc1;

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

class Doctor {
    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;
    }

    @Override
    public String toString() {
        return "Doctor [id=" + id + ", name=" + name + ", age=" + age + "]";
    }

}

public class TestJDBC {
    Connection conn; // 数据库一个连接对象
    Statement st;// 执行sql语句
    ResultSet rs;// 得到select语句执行的结果集

    public Statement getStatement() {
        // 1,加载驱动
        try {
            Class.forName("org.sqlite.JDBC");
            // 2,通过驱动管理类获得Connection对象 jdbc:sqlite:+数据库的路径  d:/test/mytest.db为数据库的位置 .db文件不存在时会创建
            conn = DriverManager.getConnection("jdbc:sqlite:d:/test/mytest.db");
            // 3,得到Statement对象
            st = conn.createStatement();

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return st;

    }

    public void closeConn() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
　　//创建表
    public void createTable() {
        st = getStatement();
        try {
            st.execute(
                    "create table if not exists doctor(_id integer primary key autoincrement,name string,age integer)");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeConn();
        }

    }
　　//添加数据
    public boolean addDoctor(String name, int age) {
        st = getStatement();
        try {
            // insert into doctor(name,age) values('张三',18);
            int count = st.executeUpdate("insert into doctor(name,age) values('" + name + "'," + age + ")");
            if (count > 0) {
                return true;
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return false;

    }
　　//更新数据
    public boolean update(String name, int age, int id) {
        st = getStatement();
        try {
            int count = st.executeUpdate("update doctor set name='" + name + "',age=" + age + "  where _id=" + id);
            if (count > 0) {
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return false;

    }
　　//删除数据
    public boolean delete(int i) {
        st = getStatement();
        try {
            int count = st.executeUpdate("delete from doctor where _id=" + i);
            if (count > 0) {// count：执行sql语句影响的行数
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeConn();
        }

        return false;
    }
　　//查询数据
    public List<Doctor> getAllDoctor() {
        st = getStatement();
        List<Doctor> list = new ArrayList<Doctor>();
        try {
            rs = st.executeQuery("select * from doctor");
            while (rs.next()) {
                Doctor doctor = new Doctor();
                doctor.setId(rs.getInt("_id"));
                doctor.setName(rs.getString("name"));
                doctor.setAge(rs.getInt("age"));
                list.add(doctor);

            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;

    }

    public static void main(String[] args) {
        TestJDBC testJDBC = new TestJDBC();
        testJDBC.createTable();
        for (int i = 0; i < 10; i++) {
            // testJDBC.addDoctor("华佗" + i, i + 10);
        }

        if (testJDBC.update("扁鹊", 1000, 1)) {
            System.out.println("修改成功！");
        }

        if (testJDBC.delete(40)) {
            System.out.println("删除成功！");
        }
        List<Doctor> list = testJDBC.getAllDoctor();
        for (Doctor doctor : list) {
            System.out.println(doctor);
        }

    }

}