JDBC的增删改查（mysql数据库）

package com.luo.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.luo.domain.User;

public class JDBCConnection {

    private static Connection getConn() {

        String driver = "com.mysql.jdbc.Driver";

        String url = "jdbc:mysql://localhost:3306/timi?useUnicode=true&characterEncoding=utf8";

        String username = "root";

        String password = "";

        Connection conn = null;

        try {

            Class.forName(driver); //classLoader,加载对应驱动

            conn = (Connection) DriverManager.getConnection(url, username, password);

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return conn;

    }

    private static int insert(User user ) {

        Connection conn = getConn();

        int i = 0;

        String sql = "insert into t_user (userId,userName,userPassword) values(?,?,?)";

        PreparedStatement pstmt;

        try {

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

            pstmt.setInt(1, user.getUserId());

            pstmt.setString(2, user.getUserName());

            pstmt.setString(3, user.getUserPassword());

            i = pstmt.executeUpdate();

            pstmt.close();

            conn.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return i;

    }

    private static int update(User user) {

        Connection conn = getConn();

        int i = 0;

        String sql = "update t_user set userName='" + user.getUserName() + "' where userId='" + user.getUserId() + "'";

        PreparedStatement pstmt;

        try {

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

            i = pstmt.executeUpdate();

            System.out.println("resutl: " + i);

            pstmt.close();

            conn.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return i;

    }

    private static Integer getAll() {

        Connection conn = getConn();

        String sql = "select * from t_user";

        PreparedStatement pstmt;

        try {

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

            ResultSet rs = pstmt.executeQuery();

            int col = rs.getMetaData().getColumnCount();

            System.out.println("============================");

            while (rs.next()) {

                for (int i = 1; i <= col; i++) {

                    System.out.print(rs.getString(i) + "\t");

                    if ((i == 2) && (rs.getString(i).length() < 8)) {

                        System.out.print("\t");

                    }

                 }

                System.out.println("");

            }

                System.out.println("============================");

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return null;

    }

    private static int delete(int userId) {

        Connection conn = getConn();

        int i = 0;

        String sql = "delete from t_user where userId='" + userId + "'";

        PreparedStatement pstmt;

        try {

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

            i = pstmt.executeUpdate();

            System.out.println("resutl: " + i);

            pstmt.close();

            conn.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return i;

    }

    public static void main(String[] args) {

         User u  = new User(8, "jdbc1", "jdbc1");

//       insert(u);

//       update(u);

//       getAll();

         delete(8);

         System.out.println("成功");

    }

}