|
在我第一次使用jdbc，來通過jsp讀取mysql中遇到一些問題記錄一下。 首先都是一個(gè)DBHelper.java的工具類， package util; import java.sql.Connection; import java.sql.DriverManager; public class DBHelper { private static final String driver = "com.mysql.jdbc.Driver";//數(shù)據(jù)庫驅(qū)動(dòng) //連接數(shù)據(jù)庫的URL地址 private static final String url = "jdbc:mysql://localhost:3306/jiang?useUnicode=true&characterEncoding=UTF-8&useSSL=false"; //數(shù)據(jù)庫的用戶名 private static final String username = "root"; //數(shù)據(jù)庫的密碼 private static final String password = "123456"; private static Connection conn = null; //靜態(tài)代碼塊負(fù)責(zé)加載驅(qū)動(dòng) static { try { Class.forName(driver); }catch(Exception ex) { ex.printStackTrace(); } } public static Connection getConnection() throws Exception { if(conn==null) { conn = DriverManager.getConnection(url, username, password); return conn; } return conn; } public static void main(String[] args) { // TODO Auto-generated method stub try { Connection conn = DBHelper.getConnection(); if(conn!=null) { System.out.println("數(shù)據(jù)庫連接正常"); }else { System.out.println("數(shù)據(jù)庫連接失敗"); } }catch(Exception ex){ ex.printStackTrace(); } } } 先記錄一下 查詢： package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import Heros.Hero; import util.DBHelper; //英雄的業(yè)務(wù)邏輯類 public class HeroDAO { public ArrayList<Hero> getAllHeros(){ Connection conn=null; PreparedStatement stmt = null; ResultSet rs = null;//數(shù)據(jù)集 ArrayList<Hero> list = new ArrayList<Hero>();//英雄集合 try { conn = DBHelper.getConnection(); String sql = "select * from hero";//Sql語句 //String sql = "select * from Hero where year=2016";//Sql語句 stmt = conn.prepareStatement(sql);//創(chuàng)建連接對(duì)象 rs = stmt.executeQuery(); while(rs.next()) { Hero hero = new Hero(); hero.setId(rs.getInt("id")); hero.setYear(rs.getString("year")); hero.setName(rs.getString("name")); hero.setPolicital(rs.getString("policital")); hero.setJob(rs.getString("job")); hero.setDieYear(rs.getDate("dieYear")); hero.setPicture(rs.getString("picture")); hero.setStory(rs.getString("story")); list.add(hero); } return list; }catch(Exception ex) { ex.printStackTrace(); return null; } finally { //釋放數(shù)據(jù)集對(duì)象 if(rs!=null) { try { rs.close(); rs=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //釋放語句對(duì)象 if(stmt!=null) { try { stmt.close(); stmt=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } //----------------------------------------------------------------- //根據(jù)英雄編號(hào)獲取英雄信息 public Hero GetHerosByID(String id) { Connection conn=null; PreparedStatement stmt = null; ResultSet rs = null;//數(shù)據(jù)集 try { conn = DBHelper.getConnection(); String sql = "select * from hero where id=?;";//Sql語句 stmt = conn.prepareStatement(sql);//創(chuàng)建連接對(duì)象 stmt.setString(1, id); //這里指定這個(gè)stmt要接收一個(gè)string類型的參數(shù) // 也可以指定stmt.setInt(1,id); 指定stmt要接收一個(gè)int類型的參數(shù)數(shù)，意思是編號(hào)從1開始 rs = stmt.executeQuery(); if(rs.next()) { Hero hero = new Hero(); hero.setId(rs.getInt("id")); hero.setYear(rs.getString("year")); hero.setName(rs.getString("name")); hero.setPolicital(rs.getString("policital")); hero.setJob(rs.getString("job")); hero.setDieYear(rs.getDate("dieYear")); hero.setPicture(rs.getString("picture")); hero.setStory(rs.getString("story")); //System.out.println(hero.getStory()); return hero; }else { return null; } }catch(Exception ex) { ex.printStackTrace(); return null; } finally { //釋放數(shù)據(jù)集對(duì)象 if(rs!=null) { try { rs.close(); rs=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //釋放語句對(duì)象 if(stmt!=null) { try { stmt.close(); stmt=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } //----------------------------------------------------------------- //----------------------------------------------------------------- //根據(jù)年份獲取英雄信息 public ArrayList<Hero> GetHerosByYear(String year) { Connection conn=null; PreparedStatement stmt = null; ResultSet rs = null;//數(shù)據(jù)集 ArrayList<Hero> list = new ArrayList<Hero>();//英雄集合 try { conn = DBHelper.getConnection(); String sql = "select * from hero where year=?;";//Sql語句 //String sql = "select * from Hero where year=2016";//Sql語句 stmt = conn.prepareStatement(sql);//創(chuàng)建連接對(duì)象 stmt.setString(1, year); rs = stmt.executeQuery(); while(rs.next()) { Hero hero = new Hero(); hero.setId(rs.getInt("id")); hero.setYear(rs.getString("year")); hero.setName(rs.getString("name")); hero.setPolicital(rs.getString("policital")); hero.setJob(rs.getString("job")); hero.setDieYear(rs.getDate("dieYear")); hero.setPicture(rs.getString("picture")); hero.setStory(rs.getString("story")); list.add(hero); } return list; }catch(Exception ex) { ex.printStackTrace(); return null; } finally { //釋放數(shù)據(jù)集對(duì)象 if(rs!=null) { try { rs.close(); rs=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //釋放語句對(duì)象 if(stmt!=null) { try { stmt.close(); stmt=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } //----------------------------------------------------------------- } 下面的是寫入（出現(xiàn)了問題）： 1，查詢的時(shí)候，用的方法是： rs = stmt.executeQuery(); 但是寫入的時(shí)候，需要用到方法是：stmt.execute(); 2，在設(shè)置sql語句的時(shí)候，一直出錯(cuò) Unknown column 'xxx' in 'field list' 。但是如果我直接從mysql中復(fù)制語句過來，比如： String sql="insert INTO `jiang`.`danmu`(`content`) VALUES ('中國加油！')"; 就沒有問題。糾結(jié)死我了。直到后來，發(fā)現(xiàn)，寫入的內(nèi)容需要用 單引號(hào)包起來。于是，我改成了：（content是形參） String sql="insert INTO danmu(content) values ('"+content+"')"; 下面是記錄我的腳本： package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import util.DBHelper; //保存數(shù)據(jù)內(nèi)容進(jìn)入到數(shù)據(jù)庫中去 public class DanMuDao { public Boolean SaveDanMu(String content) { Connection conn=null; PreparedStatement stmt = null; ResultSet rs = null;//數(shù)據(jù)集 try { conn = DBHelper.getConnection(); //String sql="insert INTO `jiang`.`danmu`(`content`) VALUES ('中國加油！')"; String sql="insert INTO danmu(content) values ('"+content+"')"; stmt = conn.prepareStatement(sql);//創(chuàng)建連接對(duì)象 stmt.execute(); }catch(Exception ex) { ex.printStackTrace(); return false; }finally { //釋放數(shù)據(jù)集對(duì)象 if(rs!=null) { try { rs.close(); rs=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } //釋放語句對(duì)象 if(stmt!=null) { try { stmt.close(); stmt=null; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return false; } } |
|
|
