Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package wiki.home;
- import java.sql.*;
- public class jdbcmysql {
- private Connection con = null; //Database objects
- //連接object
- private Statement stat = null;
- //執行,傳入之sql為完整字串
- private ResultSet rs = null;
- //結果集
- private PreparedStatement pst = null;
- //執行,傳入之sql為預儲之字申,需要傳入變數之位置
- //先利用?來做標示
- private String dropdbSQL = "DROP TABLE NE_TABLE ";
- private String createdbSQL = "CREATE TABLE NE_TABLE (" +
- " id INTEGER " +
- " , length INTEGER " +
- " , ne VARCHAR(200) " +
- " , category VARCHAR(200) " +
- " , rdd longtext)";
- private String insertdbSQL = "insert into NE_TABLE(id,length,ne,category,rdd) " +
- "select ifNULL(max(id),0)+1,?,?,?,? FROM NE_TABLE";
- private String selectSQL = "select * from NE_TABLE ";
- public void resetSelectSQL(String SQL)
- {
- this.selectSQL = SQL;
- }
- public jdbcmysql()
- {
- try {
- Class.forName("com.mysql.jdbc.Driver");
- //註冊driver
- con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Jaxon","root","iisr@1706b");
- //取得connection
- //jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=Big5
- //localhost是主機名,test是database名
- //useUnicode=true&characterEncoding=Big5使用的編碼
- }
- catch(ClassNotFoundException e)
- {
- System.out.println("DriverClassNotFound :"+e.toString());
- }//有可能會產生sqlexception
- catch(SQLException x) {
- System.out.println("Exception :"+x.toString());
- }
- }
- //建立table的方式
- //可以看看Statement的使用方式
- public void createTable()
- {
- try
- {
- stat = con.createStatement();
- stat.executeUpdate(createdbSQL);
- }
- catch(SQLException e)
- {
- System.out.println("CreateDB Exception :" + e.toString());
- }
- finally
- {
- Close();
- }
- }
- //新增資料
- //可以看看PrepareStatement的使用方式
- public void insertTable( int length,String ne,String category,String rdd)
- {
- try
- {
- pst = con.prepareStatement(insertdbSQL);
- pst.setInt(1, length);
- pst.setString(2, ne);
- pst.setString(3, category);
- pst.setString(4, rdd);
- pst.executeUpdate();
- }
- catch(SQLException e)
- {
- System.out.println("InsertDB Exception :" + e.toString());
- }
- finally
- {
- Close();
- }
- }
- //刪除Table,
- //跟建立table很像
- public void dropTable()
- {
- try
- {
- stat = con.createStatement();
- stat.executeUpdate(dropdbSQL);
- }
- catch(SQLException e)
- {
- System.out.println("DropDB Exception :" + e.toString());
- }
- finally
- {
- Close();
- }
- }
- //查詢資料
- //可以看看回傳結果集及取得資料方式
- public void SelectTable()
- {
- try
- {
- stat = con.createStatement();
- rs = stat.executeQuery(selectSQL);
- System.out.println("ID\t\tLENGTH\t\tNE\t\tCategory\tRDD");
- while(rs.next())
- {
- System.out.println(rs.getInt("id")+"\t\t"+ rs.getInt("length")+"\t\t"+
- rs.getString("ne")+"\t\t"+rs.getString("category")+"\t\t"+rs.getString("rdd"));
- }
- }
- catch(SQLException e)
- {
- System.out.println("DropDB Exception :" + e.toString());
- }
- finally
- {
- Close();
- }
- }
- //完整使用完資料庫後,記得要關閉所有Object
- //否則在等待Timeout時,可能會有Connection poor的狀況
- private void Close()
- {
- try
- {
- if(rs!=null)
- {
- rs.close();
- rs = null;
- }
- if(stat!=null)
- {
- stat.close();
- stat = null;
- }
- if(pst!=null)
- {
- pst.close();
- pst = null;
- }
- }
- catch(SQLException e)
- {
- System.out.println("Close Exception :" + e.toString());
- }
- }
- }
Add Comment
Please, Sign In to add comment