java mysql 增删改查

浏览数：31 / 时间：2015年06月12日

首先要建立和mysql的连接，Class.forName()装载这个类，然后用静态方法初始化。DriverManager.getConnection获取连接

package analysis;

import java.sql.DriverManager;
import java.sql.Connection;

public class DB {
    private static final String user = "root";
    private static final String password = "123456";
    private static final String url = "jdbc:mysql://localhost:3306/signalinfo?useUnicode=true&characterEncoding=UTF-8";
    private static Connection conn = null;

    static {
        init();
    }

    static void init() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            System.out.println("数据库连接出错:" + e);
        }

    }
    public static void closeConnection(){
        try{
            conn.close();
        }catch(Exception e){
            System.out.println("数据库关闭的时候出错" + e);
        }
    }

    public static Connection getConnection(){
        return conn;
    }
}

查找和插入以中国移动信令的数据分隔为例，要创建statement然后调用stmt.excuteQuery就会返回一个ResultSet的结果集，然后再详细操作。

插入呢，则要先有一个preparedStatement，这样配上可以escape的?的sql就可以把要插入的数据附带进去，然后通过exectueUpdate()就可以了。

package analysis;

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

public class transfer {
    final String MCC = "460";
    final String MNC = "00";
    // 数据库连接

    private static Connection conn = null;
    private static Connection newconn = null;
    //出入语句
    private static String sql = "select * from signalinfo limit 100";
    private static String sqlinsert = "insert into convertsignal(id,time,IMSI,IMEI,duration,startLAC,startCI,endLAC,endCI,cause,fromNum,toNum)"
            + "values(?,?,?,?,?,?,?,?,?,?,?,?)";
    // 数据库表达式
    private static Statement stmt = null;
    private static PreparedStatement pstmt = null;

    static void main() {
        conn = DB.getConnection();
        try {
            stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) { // 判断是否还有下一个数据
                Integer id = rs.getInt("id");
                String name = rs.getString("userNumber");
                String signal = rs.getString("signalinfo");
                String[] ss = signal.split("|");
                String time = ss[0];
                String IMSI = ss[1];
                String IMEI = ss[2];
                String dur = ss[3];
                Integer duration = Integer.parseInt(dur);
                String startLAC = ss[4];
                String startCI = ss[5];
                String endLAC = ss[6];
                String endCI = ss[7];
                String cause = ss[12];
                String fromNum = ss[15];
                String toNum = ss[16];
                pstmt = conn.prepareStatement(sqlinsert);
                pstmt.setInt(1, id);
                pstmt.setString(2,time);
                pstmt.setString(3,IMSI );
                pstmt.setString(4,IMEI );
                pstmt.setInt(5, duration);
                pstmt.setString(6,startLAC);
                pstmt.setString(7, startCI);
                pstmt.setString(8, endLAC);
                pstmt.setString(9, endCI);
                pstmt.setString(10,cause);
                pstmt.setString(11, fromNum);
                pstmt.setString(12, toNum);
                int result = pstmt.executeUpdate();
                if(result >0){
                    System.out.println("insert ok!");
                }
            }
            // 关闭数据库连接
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("!!");

    }
}