JDBC：从入门到放弃（一）

JDBC（JAVA数据库连接）是用于执行sql语句的api，可以为多种关系型数据库，提供统一访问，由一组java语言编写的类和接口组成

简单来所，jdbc主要用来做三件事：

• 与数据库建立连接

  • 加载数据库驱动程序

  Class.forName('org.sqlite.JDBC');
  

  • 建立连接

  Connection conn = DriverManager.getConnection("jdbc:sqlite:d:/stu.db","","");
  

  p1:是协议的url
  p2:参数用户名
  p3:参数密码

• 发送操作数据库的sql语句

  Statement
  PreparedStatement
  CallableStatement
  

• 处理结果

  完成向数据库发送操作语句后，数据库引擎会返回一个结果，这个结果主要有两种形式：

  • 对于更新语句，返回受影响操作的行数
  • 对于查询语句，返回查询的结果集ResultSet
    • ResultSet的next()方法下移结果集中的指针，并判断当前所指向的是否为空
    • ResultSet的getXXX()方法读取结果集中的当前记录的字段信息

JDBC基础示例：

package database;

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

public class DbTest {

    public static void main(String[] args) {
                
        List<Employee> list = fetchData();
        for (Employee employee : list) {
            System.out.println(employee);
        }
        
        Employee employee = login("jack", "456");
        if (employee != null) {
            System.out.println("登陆成功！欢迎：" + employee.getName());
        } else {
            System.out.println("登陆失败！");
        }
    }
    
    /**
     * 
     * @param name
     * @param pwd
     * @return 登陆账户信息
     */
    private static Employee login(String name, String pwd) {

        Employee employee = null;
        Connection conn = null;

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:d:/sqliteDB/company.db");
            PreparedStatement prepareStatement = conn
                    .prepareStatement("select * from employee where name = ? and pwd = ?");

            prepareStatement.setString(1, name);
            prepareStatement.setString(2, pwd);
            ResultSet rs = prepareStatement.executeQuery();

            if (rs.next()) {
                employee = new Employee(rs.getString("name"), rs.getInt("id"), rs.getString("pwd"), rs.getInt("age"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return employee;

    }

    private static List<Employee> fetchData() {

        List<Employee> arrayList = new ArrayList<Employee>();
        Employee employee = null;
        Connection conn = null;

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:d:/sqliteDB/company.db");
            Statement statement = conn.createStatement();
            String string = "select * from employee";
            ResultSet rs = statement.executeQuery(string);

            while (rs.next()) {
                employee = new Employee(rs.getString("name"), rs.getInt("id"), rs.getString("pwd"), rs.getInt("age"));
                arrayList.add(employee);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return arrayList;
    }

}

这里大家记得导入sqlite-jdbc-jar的包哦，我的sqlite的版本是3.20，这里有一个sqlite连接jdbc的jar包，各个版本都有，大家可以到这里下载。