Java 之jdbc连接mysql数据库

package jdbc;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

public class JdbcUtils

{

    private static String url;

    private static String user;

    private static String password;

    static {

        try {

            //FileInputStream in = new FileInputStream(new File("./src/db.properties"));

            InputStream in = JdbcUtils.class.getResourceAsStream("/db.properties");

            Properties properties = new Properties();

            properties.load(in);

            Class.forName(properties.getProperty("driver"));

            url = properties.getProperty("url");

            user = properties.getProperty("user");

            password = properties.getProperty("password");

        } catch (Exception e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        }

    }

    /**

     * 获取连接

     * @return

     */

    public static Connection getConnection() {

        try {

            return DriverManager.getConnection(url, user, password);

        } catch (SQLException e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        }

    }

    /**

     * 关闭连接

     */

    public static void close(Statement stateMent, Connection connection) {

        if (stateMent != null) {

            try {

                stateMent.close();

            } catch (SQLException e) {

                e.printStackTrace();

                throw new RuntimeException(e);

            }

        }

        if (connection != null) {

            try {

                connection.close();

            } catch (SQLException e) {

                e.printStackTrace();

                throw new RuntimeException(e);

            }

        }

    }

}

package jdbc.statement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import jdbc.JdbcUtils;

import org.junit.Test;

public class StateMentTest {

    @Test

    public void testInsert() {

        Connection conn = null;

        Statement stmt = null;

        try {

            conn = JdbcUtils.getConnection();

            stmt = conn.createStatement();

            String sql = "insert into test (name) values ('test')";

            int count = stmt.executeUpdate(sql);

            System.out.println(count);

        } catch (Exception e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally {

            JdbcUtils.close(stmt, conn);

        }

    }

    @Test

    public void testUpdate() {

        Connection conn = null;

        Statement stmt = null;

        try {

            conn = JdbcUtils.getConnection();

            stmt = conn.createStatement();

            String sql = "update test set name = 'update' where id=4";

            int count = stmt.executeUpdate(sql);

            System.out.println(count);

        } catch (Exception e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally {

            JdbcUtils.close(stmt, conn);

        }

    }

    @Test

    public void testDelete() {

        Connection conn = null;

        Statement stmt = null;

        try {

            conn = JdbcUtils.getConnection();

            stmt = conn.createStatement();

            String sql = "delete from test where id=4";

            int count = stmt.executeUpdate(sql);

            System.out.println(count);

        } catch (Exception e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally {

            JdbcUtils.close(stmt, conn);

        }

    }

    @Test

    public void testSelect() {

        Connection conn = null;

        Statement stmt = null;

        try {

            conn = JdbcUtils.getConnection();

            stmt = conn.createStatement();

            String sql = "select * from test";

            ResultSet resultSet = stmt.executeQuery(sql);

            while(resultSet.next()) {

                System.out.println(resultSet.getString("name"));

            }

        } catch (Exception e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally {

            JdbcUtils.close(stmt, conn);

        }

    }

}

package jdbc.prepared;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import jdbc.JdbcUtils;

import org.junit.Test;

public class PreparedStateMent {

    @Test

    public void testInsert()

    {

        Connection conn = JdbcUtils.getConnection();

        String sql = "insert into test (name) values (?)";

        PreparedStatement pstmt = null;

        try {

            pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

            pstmt.setString(1, "prepared");

            int count = pstmt.executeUpdate();

            ResultSet resultSet = pstmt.getGeneratedKeys();

            if (resultSet.next()) {

                System.out.println(resultSet.getString(1));

            }

            System.out.println(count);

        } catch (SQLException e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally{

            JdbcUtils.close(pstmt, conn);

        }

    }

    @Test

    public void testUpdate()

    {

        Connection conn = JdbcUtils.getConnection();

        String sql = "update test set name = ? where id = ?";

        PreparedStatement pstmt = null;

        try {

            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, "prepareds");

            pstmt.setInt(2, 6);

            int count = pstmt.executeUpdate();

            System.out.println(count);

        } catch (SQLException e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally{

            JdbcUtils.close(pstmt, conn);

        }

    }

    @Test

    public void testDelete()

    {

        Connection conn = JdbcUtils.getConnection();

        String sql = "delete from test where id = ?";

        PreparedStatement pstmt = null;

        try {

            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, 6);

            int count = pstmt.executeUpdate();

            System.out.println(count);

        } catch (SQLException e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally{

            JdbcUtils.close(pstmt, conn);

        }

    }

    @Test

    public void testSelect()

    {

        Connection conn = JdbcUtils.getConnection();

        String sql = "select * from test";

        PreparedStatement pstmt = null;

        try {

            pstmt = conn.prepareStatement(sql);

            ResultSet set = pstmt.executeQuery();

            while(set.next()) {

                System.out.println(set.getString("name"));

            }

        } catch (SQLException e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally{

            JdbcUtils.close(pstmt, conn);

        }

    }

}