Java JDBC连接数据库实例

问题描述：向数据库按照一定规则增加记录，显示一条一条记录向数据库中增加不是好的方式，应该由程序去实现。
现在给出实例：
JdbcUtils工具类：

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.Properties;

/**
 * 
 * JDBC工具类，回去数据库连接和释放连接
 */
public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    // 加载驱动，获取数据库连接信息
    static {
        try {
            // 加载配置文件
            InputStream in = JdbcUtils.class.getClassLoader()
                    .getResourceAsStream("DB.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            // 加载驱动
            Class.forName(driver);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 
     * 获取数据库连接
     * 
     * @throws SQLException
     */
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 释放资源
     * 
     * @param connection
     * @param preparedStatement
     * @param resultSet
     */
    public static void releaseDB(Connection connection,
            PreparedStatement preparedStatement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

}

DB.properties 配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://目标URL/数据库
username=#your 用户名
password=# your 密码

JdbcTest CRUD 操作

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcTest {

    /*
     * 增加
     */
    public void create() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = JdbcUtils.getConnection();

            String carnumber_head = "甬-K-1F-";
            for (int i = 1; i <= 100; i++) {

                if (i < 10) {
//                  System.out.println("value------>>>" + carnumber_head+"000" + i);
                     // 准备sql语句
                    String sql = "INSERT INTO cx_carnumberpool(carnumber) VALUES(?)";
                    // 获取PrepareStatement对象
                    preparedStatement = connection.prepareStatement(sql);
                    // 填充占位符
                    preparedStatement.setString(1, carnumber_head+"000" + i);
                    int num = preparedStatement.executeUpdate();// 返回影响到的行数

                    System.out.println("一共影响到" + num + "行");

                } else if (i < 100) {
                     // 准备sql语句
                    String sql = "INSERT INTO cx_carnumberpool(carnumber) VALUES(?)";
                    // 获取PrepareStatement对象
                    preparedStatement = connection.prepareStatement(sql);
                    // 填充占位符
                    preparedStatement.setString(1, carnumber_head+"00" + i);
                    int num = preparedStatement.executeUpdate();// 返回影响到的行数

                    System.out.println("一共影响到" + num + "行");
                } else if (i < 1000) {
                     // 准备sql语句
                    String sql = "INSERT INTO cx_carnumberpool(carnumber) VALUES(?)";
                    // 获取PrepareStatement对象
                    preparedStatement = connection.prepareStatement(sql);
                    // 填充占位符
                    preparedStatement.setString(1, carnumber_head+"0" + i);
                    int num = preparedStatement.executeUpdate();// 返回影响到的行数
                    System.out.println("一共影响到" + num + "行");
                } 

            }




        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseDB(connection, preparedStatement, null);
        }
    }

    /*
     * 读取查询
     */
    public void retrieve() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT user_name,user_password,user_birth FROM user";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            // 遍历结果集
            while (resultSet.next()) {
                String username = resultSet.getString(1);
                String password = resultSet.getString(2);
                Date userbirth = resultSet.getDate(3);

                System.out.println(username + ":" + password + ":" + userbirth);
            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            JdbcUtils.releaseDB(connection, preparedStatement, resultSet);
        }

    }

    /*
     * 修改更新
     */
    public void update() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "UPDATE USER SET user_password = ? WHERE user_name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "passwordupdate");
            preparedStatement.setString(2, "mary");
            int num = preparedStatement.executeUpdate();

            System.out.println("一共影响到" + num + "行");
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            JdbcUtils.releaseDB(connection, preparedStatement, null);
        }
    }

    /*
     * 删除
     */
    public void delete() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "DELETE FROM user WHERE user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 3);
            int num = preparedStatement.executeUpdate();

            System.out.println("一共影响到" + num + "行");
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            JdbcUtils.releaseDB(connection, preparedStatement, null);
        }

    }

    public static void main(String[] args) {

         JdbcTest test = new JdbcTest();
         test.create();

    }

}

实例会上传至CSDN 资源中