java JDBC连接数据库

JDBC（Java Data Base Connection） API由原SUN公司提供，
提供了Java应用程序与各种不同数据库交互的标准接口。
如：Connection（连接）接口、Statement接口
、PreparedStatement接口、ResultSet（结果集）接口等。
可以使用这些JDBC接口进行各类数据库操作。

JDBC链接数据库的基本操作
1、注册驱动（以MYSQL为例）

Class.forName("com.mysql.jdbc.Driver");

2、创建链接

String url = "jdbc:mysql://localhost:3306/student?useSSL=false";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

3、 创建执行对象

stmt = conn.createStatement();

4、 操作数据库

    String sql = "select * from stuinfo";
    rs = stmt.executeQuery(sql);

1. 获取结果

while (rs.next()) {             System.out.println(rs.getString("StuName"));
}

6、关闭资源

try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

执行对象解析时，

关于PreparedStatement 的用法：

String sql = "select * from stuinfo where stuid = ?";
pstmt = conn.prepareStatement(sql);
Scanner sc = new Scanner(System.in);
String stuid = sc.next();
pstmt.setString(1, stuid);
rs = pstmt.executeQuery();

下面的代码可用于jsp中的数据库连接时使用：

public class DButils {
    private static final String URL = "jdbc:mysql://localhost:3306/pss?useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "123456yin";
    private static final String DRIVER = "com.mysql.jdbc.Driver";

    //静态块，用于注册数据库驱动
    static {
        try {
            Class.forName(DRIVER);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    //数据库连接
    public static Connection getConnection()
    {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (Exception e) {
            // TODO: handle exception
        e.printStackTrace();
        }
        return conn;
    }

    //数据库关闭，用于需要获取结果时
    public static void close(ResultSet rs,PreparedStatement state,Connection conn)
    {
        try {
            if(rs != null)
                rs.close();
            if(state != null)
                state.close();
            if(conn != null)
                conn.close();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

    }

//数据库关闭，用于不需要获取结果时
    public static void close(PreparedStatement state,Connection conn)
    {
        try {
            if(state != null)
                state.close();
            if(conn != null)
                conn.close();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

    }
}

在数据库中的product表中按id查询并返回查询到的结果

public productinfo queryById(int proID)
    {
        productinfo pro = new productinfo();
        Connection conn = DButils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        if(conn != null)
        {
            try {
                String sql = "select * from product where prod_id = ?";
                state = conn.prepareStatement(sql);
                state.setInt(1, proID);
                rs = state.executeQuery();
                if(rs.next())
                {
                    pro.setProd_id(rs.getInt("prod_id"));
                    pro.setProd_name(rs.getString("prod_name"));
                    pro.setProd_supplierid(rs.getInt("prod_supplierid"));
                    pro.setProd_inprice(rs.getDouble("prod_inprice"));
                    pro.setProd_outprice(rs.getDouble("prod_outprice"));
                    pro.setProd_wareid(rs.getInt("prod_wareid"));
                    pro.setProd_num(rs.getInt("prod_num"));
                }

            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally{
                DButils.close(rs,state, conn);
            }
        }
        return pro;
    }

删除数据库中的某一条信息

    public int delete(int proID) {
        // TODO Auto-generated method stub
        productinfo pro = new productinfo();
        Connection conn = DButils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        int flag = 0;
        if(conn != null)
        {
            try {
                String sql = "delete from product where prod_id = ?";
                state = conn.prepareStatement(sql);
                state.setInt(1, proID);
                state.executeUpdate();
                flag=1;
            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally{
                DButils.close(state, conn);
            }
        }
        if(flag == 1)
            return 1;
        else
            return 0;
    }