Eclipse下 Java对MySQL数据库操作

发布时间:2019-05-02 | 分类:MySql

标签: 

1. 下载Mysql驱动。

Link:http://dev.mysql.com/downloads/connector/j/

2.加载JDBC

在Eclipse中,选中相应的工程,Properties->Java Build path->add external jars->ok.

3.JDBC操作数据库的一般步骤

注册驱动 (只做一次)
 
Class.forName(“com.mysql.jdbc.Driver”);
 
建立连接(Connection)
 
Connection conn = DriverManager.getConnection(url, user, password);
url格式:JDBC:子协议:子名称//主机名:端口/数据库名?属性名=属性值&…
User,password可以用“属性名=属性值”方式告诉数据库;
 
example:

String url="jdbc:mysql://localhost:3306/test";

Connection conn;

conn = DriverManager.getConnection(url,"root","root");
创建执行SQL的语句(Statement)
 
执行语句
 
Statement是一个SQL执行器,可以用来执行一个静态的SQL语句。
Statement st = conn.createStatement();
st.executeQuery(sql);
 
example:

Statement stmt = conn.createStatement(); //创建Statement对象
String sql= "select * from writers";
ResultSet rs = stmt.executeQuery(sql);

执行存储过程
CallableStatement(从PreperedStatement扩展来)
cs = connection.prepareCall(“{call psname(?,?,?)}”);
cs.registerOutParameter(index, Types.INTEGER);
cs.setXXX(i, xxxx);
cs.executeUpdate();
int id=cs.getInt(index);
 
处理执行结果(ResultSet)
 
ResultSet表示一个查询结果集。
ResultSet rs = statement.executeQuery(sql);
While(rs.next()){
 rs.getString(“col_name”);
 rs.getInt(“col_name”);
 //…
}
释放资源
 
释放资源的顺序是ResultSet, Statement,Connection;
Connection在使用完成后,必须关闭,ResultSet, Statement无所谓,只要Connection关闭了,它们也会被自动关闭(但资源不是立即被释放)。
Connection的使用原则是尽量晚创建,尽量早的释放。
 
 
 
 
例子:
package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class connect {
    
      private Connection conn = null;
      private Statement stmt = null;
      private PreparedStatement preparedStatement = null;
      private ResultSet rs = null;
      
      
    public void readDataBase()  throws Exception{
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
             System.out.println("成功加载MySQL驱动!");
        }catch(ClassNotFoundException e1){
            System.out.println("找不到MySQL驱动!");
            e1.printStackTrace();
        }

        String url="jdbc:mysql://localhost:3306/test";    //JDBC的URL    
       

        
        try {

             //调用DriverManager对象的getConnection()方法,获得一个Connection对象
                conn = DriverManager.getConnection(url,"root","root");
                //创建一个Statement对象
                stmt = conn.createStatement(); //创建Statement对象
                System.out.println("成功连接到数据库!");
                
                String sql= "select * from writers";
                rs = stmt.executeQuery(sql);
                writeMetaData(rs);
                writeResultSet(rs);
                
                String sql2= "insert into writers values(?,?)";
                preparedStatement=conn.prepareStatement(sql2);
                
                preparedStatement.setString(1, "11");
                preparedStatement.setString(2, "TestEmail");
                preparedStatement.executeUpdate();
                
                rs = stmt.executeQuery(sql);
                writeMetaData(rs);
                writeResultSet(rs);
                
                
        } catch (Exception e) {
              throw e;
        } 
    }
    
    private void writeMetaData(ResultSet rs)throws SQLException{
        System.out.println("The columns in the table are: ");
        
        System.out.println("Table: " + rs.getMetaData().getTableName(1));
        for  (int i = 1; i<= rs.getMetaData().getColumnCount(); i++){
          System.out.print( rs.getMetaData().getColumnName(i)+"\t");
        }
        System.out.println();
    }
    public void writeResultSet(ResultSet rs) throws SQLException{
        
        while(rs.next()){
            System.out.print(rs.getString(1)+"\t");
            System.out.print(rs.getString(2));
            System.out.println();
        }
        
    }
      private void close() {
            try {
              if (rs != null) {
                rs.close();
              }

              if (stmt != null) {
                stmt.close();
              }

              if (conn != null) {
                conn.close();
              }
            } catch (Exception e) {

            }
          }
}

 

main:
import com.jdbc.connect;
public class Main {

    public static void main(String[] args) throws Exception {
        // TODO Auto-generated method stub
        connect ms= new connect();
        ms.readDataBase();
    }

}

 

 

java编程学习网专注于java编程技术分享