JDBC连接数据库

db.properties配置文件(MySql数据库)

# db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=123456

# paramter for BasicDataSource
initSize=2
maxActive=2

db.properties配置文件(Oracle数据库)

# db.properties
jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:localhost:1521:orcl
jdbc.username=root
jdbc.password=123456

# paramter for BasicDataSource
initSize=2
maxActive=2

JDBC直接连接数据库

package JDBC;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * JDBC连接数据库管理工具类
 */
public class JDBC {
    static String driver;
    static String url;
    static String username;
    static String password;

    static {
        try {   
            Properties cfg = new Properties();
            InputStream in = JDBC.class.getClassLoader().getResourceAsStream("db.properties");
            cfg.load(in);   //将文件内容加载到Properties对象中(以散列表形式存在)
            driver = cfg.getProperty("jdbc.driver");
            url = cfg.getProperty("jdbc.url");
            username = cfg.getProperty("jdbc.username");
            password = cfg.getProperty("jdbc.password");
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 创建数据库连接
     */
    public static Connection getConnection() {
        try {
            Class.forName(driver);  //注册驱动
            Connection conn = DriverManager.getConnection(url, username, password);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /*
     * 关闭数据库的连接
     */
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

通过连接池连接数据库

package JDBC;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * 连接池版本的数据库连接管理工具类
 */
public class DBUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static int initSize;
    private static int maxActive;
    private static BasicDataSource dbs;

    static {
        dbs = new BasicDataSource();
        Properties cfg = new Properties();
        try {
            InputStream in = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
            cfg.load(in);
            // 初始化参数
            driver = cfg.getProperty("jdbc.driver");
            url = cfg.getProperty("jdbc.url");
            username = cfg.getProperty("jdbc.username");
            password = cfg.getProperty("jdbc.password");
            initSize = Integer.parseInt(cfg.getProperty("initSize"));
            maxActive = Integer.parseInt(cfg.getProperty("maxActive"));
            in.close();
            // 初始化连接池
            dbs.setDriverClassName(driver);
            dbs.setUrl(url);
            dbs.setUsername(username);
            dbs.setPassword(password);
            dbs.setInitialSize(initSize);
            dbs.setMaxActive(maxActive);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 创建数据库连接,从连接池中获取连接,如果连接池满了,则等待.
     */
    public static Connection getConnection() {
        try {
            Connection conn = dbs.getConnection();
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /*
     * 关闭数据库的连接,归还到连接池
     */
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //回滚，仅在禁用自动提交时使用
    public static void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

连接数据库后的使用

Connection conn=null;
try{
    conn=DbUtils.getConnection();
    Statement st=conn.createStatement();
    String sql="select id, name from people";
    ResultSet rs=st.executeQuery(sql);
    while(rs.next()){
        int id=rs.getInt("id");
        String name=rs.getString("name");
        System.out.println(id+","+name);
    }
    //结果集元数据
    ResultSetMetaData meta = rs.getMetaData();
    int n = meta.getColumnCount();  //多少列
    for(int i=1; i<=n; i++){
        String name= meta.getColumnName(i);  //获取列名
        System.out.println(name);
    }
    rs.close();//释放查询结果
    st.close();//释放语句对象
}catch(Exception e){
    e.printStackTrace();
}finally {
    DbUtils.close(conn);
}

预编译SQL执行 及 取消自动提交

try {
    conn = DBUtils.getConnection();
    conn.setAutoCommit(false);   //取消自动提交, 后续手动提交
    String sql="update people set name=? where id=? ";
    PreparedStatement ps= conn.prepareStatement(sql);
    //按照顺序发送参数
    ps.setString(1, "Lao Wang");
    ps.setInt(2, 100);
    //执行"执行计划"
    int n=ps.executeUpdate();  
    conn.commit();   //手动提交
} catch (Exception e) {
    e.printStackTrace();
    DBUtils.rollback(conn);  //异常回滚
}finally{
    DBUtils.close(conn);
}

Statement的addBatch(sql)和executeBatch()方法可以批量执行sql。

Statement st=conn.createStatement();
st.addBatch(sql1);  //sql1 添加到Statement的缓存中 
st.addBatch(sql2);
st.addBatch(sql3);

int[] ary=st.executeBatch();    //执行一批SQL

PreparedStatement也支持批量参数的处理

PreparedStatement ps =  conn.prepareStatement(sql);
ps.setInt(1, 1);
ps.setString(2, "wang");
ps.addBatch();      //将参数添加到ps缓存区

ps.setInt(1, 2);
ps.setString(2, "li");
ps.addBatch();      //将参数添加到ps缓存区

int[] ary = ps.executeBatch();  // 批量执行

PreparedStatement ps = conn.prepareStatement(sql);
还可以传入第二个参数用以获取自增主键或者序号自增的列