JDBC 操作mysql 實現增刪查改

標籤：

package com.eduk.demo;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JDBCOperation {
    private static  Connection conn = null;//聲明資料庫回話對象,Connection 這是用來維護資料庫連接資訊
    private static  Statement sts = null;// Statement   用來操作資料，也是執行SQL語句（父類）
    
    
    static class Student {
        private String Id;
        private String Name;
        private String Sex;
        private String Age;
        private String Course;
        private String Time;

       Student(String Id, String Name, String Sex, String Age,String Course,String Time) {
            this.Id = Id; //default
            this.Name = Name;
            this.Sex = Sex;
            this.Age = Age;
            this.Course = Course;
            this.Time = Time;
        }

        public String getCourse() {
            return Course;
        }

        public void setCourse(String course) {
            Course = course;
        }

        public String getTime() {
            return Time;
        }

        public void setTime(String time) {
            Time = time;
        }

        public void setAge(String age) {
            Age = age;
        }

        public String getId() {
            return Id;
        }

        public void setId(String Id) {
            this.Id = Id;
        }

        public String getName() {
            return Name;
        }

        public void setName(String Name) {
            this.Name = Name;
        }

        public String getSex() {
            return Sex;
        }

        public void setSex(String Sex) {
            this.Sex = Sex;
        }

        public String getAge() {
            return Age;
        }

        public void setage(String Age) {
            this.Age = Age;
        }
}
    
    
    private static Connection getConn() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/uus";
        String username = "root";
        String password = "root";
        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 CreateDatabase(String database) {
        Connection conn = getConn();
        int i = 0;
        String sql = "CREATE DATABASE "+database;
        //String sql = "DROP DATABASE "+database;
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            System.out.println("建立資料庫" + database + "成功！");
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println("建立資料表失敗！");
            e.printStackTrace();
        }
        return i;
    }
    
    
    
    private static int CreateTable() {
        Connection conn = getConn();
        int i = 0;
        //        UPDATE students set age="23" where name="Achilles";
        //        UPDATE users SET age = 24, name = ‘Mike‘ WHERE id = 123;
          String sql = "CREATE TABLE student " +
          "(sid INTEGER(20) not NULL, " +
          " name VARCHAR(255) not NULL, " +
          " sex VARCHAR(255), " +
          " age INTEGER(4), " +
          " course FLOAT(8), " +
          " time VARCHAR(32), " +
          " PRIMARY KEY ( sid ))ENGINE=InnoDB DEFAULT CHARSET=UTF8; ";
       // String sql = "update student set name=‘" + student.getName() + "‘ where sid=" + student.getId();
        //String sql = "update student set name=‘送終極‘ where sid=12;";
            try {

                sts = conn.createStatement();//初始化statement
                int f = sts.executeUpdate(sql);
        
                System.out.println("建立資料表成功！");
            } catch (SQLException e) {
                System.out.println("建立資料表失敗！");
                e.printStackTrace();
            }
            finally {
                try {
                //    res.close();//可寫可不寫
                    sts.close();//可寫可不寫
                    conn.close();//一定要寫
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return 0;
    }
    
    
    
    private static int insert(Student student) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into student (SID,Name,Sex,Age,Course,Time) values(?,?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, student.getId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getSex());
            pstmt.setString(4, student.getAge());
            pstmt.setString(5, student.getCourse());
            pstmt.setString(6, student.getTime());
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
            System.out.println("插入成功！");
        } catch (SQLException e) {
            System.out.println("插入失敗！");
            e.printStackTrace();
        }
        return i;
    }
    
    
    private static int update(Student student) {
        Connection conn = getConn();
        int i = 0;
        //        UPDATE students set age="23" where name="Achilles";
        //        UPDATE users SET age = 24, name = ‘Mike‘ WHERE id = 123;
       String sql = "update student set Name=‘" + student.getName() + "‘,sex=‘" + student.getSex() + "‘, age = ‘" +
        student.getAge() + "‘,course = ‘" + student.getCourse() + "‘, time = ‘" + student.getTime()   + "‘ where sid=‘" + student.getId() + "‘";
       // String sql = "update student set name=‘" + student.getName() + "‘ where sid=" + student.getId();
        //String sql = "update student set name=‘送終極‘ where sid=12;";
        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 student order by sid desc";
        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(String name) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete from students where Name=‘" + name + "‘";
        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[]) {
    //    JDBCOperation.CreateDatabase("uus");
    //    JDBCOperation.CreateTable();
        JDBCOperation.insert(new Student("8","陽春白雪", "女", "23","95",""));
       // JDBCOperation.getAll();
      //  JDBCOperation.update(new Student("12","JAC", "Femail","26", "90",""));
     //   JDBCOperation.update(new Student("4","歐陽春","男","29", "90",""));
       // JDBCOperation.delete("Achilles");
        JDBCOperation.getAll();
    }
}

JDBC 操作mysql 實現增刪查改