在我第一次使用jdbc，來(lái)通過(guò)jsp讀取mysql中遇到一些問(wèn)題記錄一下。

首先都是一個(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ù)庫(kù)驅(qū)動(dòng)

//連接數(shù)據(jù)庫(kù)的URL地址

private static final  String url = "jdbc:mysql://localhost:3306/jiang?useUnicode=true&characterEncoding=UTF-8&useSSL=false";

//數(shù)據(jù)庫(kù)的用戶名

private static final  String username = "root";

//數(shù)據(jù)庫(kù)的密碼

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ù)庫(kù)連接正常");

}else {

System.out.println("數(shù)據(jù)庫(kù)連接失敗");

}

}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語(yǔ)句

//String sql = "select * from  Hero where year=2016";//Sql語(yǔ)句

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();

}

}

//釋放語(yǔ)句對(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語(yǔ)句

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開(kāi)始

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();

}

}

//釋放語(yǔ)句對(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語(yǔ)句

//String sql = "select * from  Hero where year=2016";//Sql語(yǔ)句

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();

}

}

//釋放語(yǔ)句對(duì)象

if(stmt!=null) {

try {

stmt.close();

stmt=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

//-----------------------------------------------------------------

}

下面的是寫入（出現(xiàn)了問(wèn)題）：

1，查詢的時(shí)候，用的方法是：   rs = stmt.executeQuery();  但是寫入的時(shí)候，需要用到方法是：stmt.execute();

2，在設(shè)置sql語(yǔ)句的時(shí)候，一直出錯(cuò) 

Unknown column 'xxx' in 'field list'  。

但是如果我直接從mysql中復(fù)制語(yǔ)句過(guò)來(lái)，比如：

String sql="insert INTO `jiang`.`danmu`(`content`) VALUES ('中國(guó)加油！')"; 

就沒(méi)有問(wèn)題。糾結(jié)死我了。直到后來(lái)，發(fā)現(xiàn)，寫入的內(nèi)容需要用 單引號(hào)包起來(lái)。于是，我改成了：（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ù)庫(kù)中去

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 ('中國(guó)加油！')";

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();

}

}

}

//釋放語(yǔ)句對(duì)象

if(stmt!=null) {

try {

stmt.close();

stmt=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return false;

}

}