java使用jdbc操作数据库

public void con(){
    //把几个用于连接数据库的字符串定义成常量，不必每次去创建,这些字符串也可以通过配置文件方式读取
    String USER = "root";//数据库用户名
    String UPWD = "root";//数据库密码
    //本地数据库shop
    String URL = "jdbc:mysql://localhost:3306/db_book";
    //驱动
    String DRIVER = "com.mysql.jdbc.Driver";
    Statement st = null;
    Connection con = null;
    try {
      //加载驱动
      Class.forName(DRIVER);
      //获取连接对象
      con = DriverManager.getConnection(URL, USER, UPWD);
      //获取执行对象
      st = con.createStatement();
      String sqlString = "create table stu(id int,name varchar(25),age int)";
      //执行sql
      int row = st.executeUpdate(sqlString);
      System.err.println(row);
      
    } catch (Exception e) {
      try {
        if(st != null) {
          st.close();
        }
      } catch (SQLException e2) {
        e2.printStackTrace();
      }
      try {
        if(con != null) {
          con.close();
        }
      } catch (SQLException e3) {
        e3.printStackTrace();
      }
    }
  }

String sqlString = "insert into stu(name,age)value('123',20)";
      //执行sql
      st.executeUpdate(sqlString,st.RETURN_GENERATED_KEYS);
      
      ResultSet resultSet = st.getGeneratedKeys();
      if(resultSet.next()) {
        int id = resultSet.getInt(1);
        System.out.println(id);
      }

String sqlString = "insert into stu(name,age)value(?,?)";
      
      PreparedStatement pStatement = con.prepareStatement(sqlString,Statement.RETURN_GENERATED_KEYS);
      pStatement.setString(1, "123");
      pStatement.setInt(2, 20);
      pStatement.executeUpdate();
      ResultSet resultSet = pStatement.getGeneratedKeys();
      if(resultSet.next()) {
        int id = resultSet.getInt(1);
        System.out.println(id);
      }

package pojo;
 
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
 
/**
 * 封装的jdbc工具类
 * 
 * @author Administrator
 *
 */
public class MyJDBCUtil {
  // 定义数据库连接对象
  private static Connection con;
  // 定义执行sql对象
  private static PreparedStatement ps;
  // 定义结果集对象
  private static ResultSet rs;
  private static String driver;
  private static String url;
  private static String username;
  private static String password;
 
  // 工具类的构造器私有了，不让外部调用
  private MyJDBCUtil() {
  }
 
  // 下面的数据可以通过配置文件读取
  static {
    try {
      readConfig();
      Class.forName(driver);
      con = DriverManager.getConnection(url, username, password);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
 
  // 读取配置文件
  private static void readConfig() {
    InputStream in = MyJDBCUtil.class.getClassLoader().getResourceAsStream("database.properties");
    System.out.println(in);
    Properties properties = new Properties();
    try {
      properties.load(in);
      driver = properties.getProperty("driver");
      url = properties.getProperty("url");
      username = properties.getProperty("username");
      password = properties.getProperty("password");
    } catch (IOException e) {
      e.printStackTrace();
    }
 
  }
 
  // 提供获取连接对象的公共方法
  public static Connection getConnection() {
    return con;
  }
 
  // 查询操作
  public static ResultSet excuteQuery(String sql, List params) throws SQLException {
    if (con == null || con.isClosed())// 连接对象是空 或者已经打开了就重新开启
    {
      getConnection();
    }
    ps = con.prepareStatement(sql);
    if (params != null && params.size() > 0) {
      for (int i = 0; i < params.size(); i++) {
        ps.setObject(i + 1, params.get(i));
      }
    }
    rs = ps.executeQuery();
    return rs;
  }
 
  // 更新操作
  public static int excuteUpdate(String sql, List params) throws SQLException {
    if (con == null || con.isClosed())// 连接对象是空 或者已经打开了就重新开启
    {
      getConnection();
    }
    ps = con.prepareStatement(sql);
    if (params != null && params.size() > 0) {
      for (int i = 0; i < params.size(); i++) {
        ps.setObject(i + 1, params.get(i));
      }
    }
    return ps.executeUpdate();
  }
 
  // 关闭资源
  public static void closeAll() {
    try {
      if (rs != null && !rs.isClosed()) {
        rs.close();
      }
      if (ps != null && !ps.isClosed()) {
        ps.close();
      }
      if (con != null && !con.isClosed()) {
        con.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
 
}

package pojo;
 
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
 
 
public class DBCPUtils {
  private static DataSource dataSource;
  static{
    try {
      //1.加载找properties文件输入流
      InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("database.properties");
      //(也可以使用流的方式加载)
      //FileInputStream is=  new FileInputStream("database.properties");
      //2.加载输入流
      Properties props = new Properties();
      props.load(is);
      //3.创建数据源
      dataSource = BasicDataSourceFactory.createDataSource(props);
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }
  
  public static DataSource getDataSource(){
    return dataSource;
  }
  
  public static Connection getConnection(){
    try {
      return dataSource.getConnection();
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
}
 
测试
public class Test {
  public static void main(String[] args) {
    System.out.println(DBCPUtils.getDataSource());
    System.out.println(DBCPUtils.getConnection());
  }
}

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <!-- 默认配置 -->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
  <property name="jdbcUrl">jdbc:mysql:///db_jdbcdemo</property>
  <property name="user">root</property>
  <property name="password">root</property>
  <property name="initialPoolSize">5</property>
  <property name="maxPoolSize">20</property>
  </default-config>
  <!-- 命名配置 -->
  <named-config name="lph"> 
    <property name="driverClass">com.mysql.jdbc.Driver</property>
  <property name="jdbcUrl">jdbc:mysql:///db_jdbcdemo</property>
  <property name="user">root</property>
  <property name="password">root</property>
  </named-config>
</c3p0-config>

package c3p0;
 
import java.sql.Connection;
import java.sql.SQLException;
 
import javax.sql.DataSource;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
public class C3P0Utils {
  //使用默认配置
  //private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
  //使用命名配置
  private static ComboPooledDataSource dataSource = new ComboPooledDataSource("lph");
  //获得连接池（数据源）
  public static DataSource getDataSource() {
    return dataSource;
  }
  //获得连接
  public static Connection getConn() {
    try {
      return dataSource.getConnection();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("连接获取错误");
    }
  }
}
 
测试
public class Test {
  public static void main(String[] args) {
    //获取连接池
    System.out.println(C3P0Utils.getDataSource());
    //获取连接对象
    System.out.println(C3P0Utils.getConn());
  }
}