【原】在处理jsp读取mysql中遇到的问题记录

在我第一次使用jdbc，来通过jsp读取mysql中遇到一些问题记录一下。

首先都是一个DBHelper.java的工具类，

package util;

import java.sql.Connection;

import java.sql.DriverManager;

public class DBHelper {

private static final String driver = "com.mysql.jdbc.Driver";//数据库驱动

//连接数据库的URL地址

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

//数据库的用户名

private static final  String username = "root";

//数据库的密码

private static final  String password = "123456";

private static Connection conn = null;

//静态代码块负责加载驱动

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("数据库连接正常");

}else {

System.out.println("数据库连接失败");

}

}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;

//英雄的业务逻辑类

public class HeroDAO {

public ArrayList<Hero> getAllHeros(){

Connection conn=null;

PreparedStatement stmt = null;

ResultSet rs = null;//数据集

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);//创建连接对象

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 {

//释放数据集对象

if(rs!=null) {

try {

rs.close();

rs=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

//释放语句对象

if(stmt!=null) {

try {

stmt.close();

stmt=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

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

//根据英雄编号获取英雄信息

public Hero GetHerosByID(String id) {

Connection conn=null;

PreparedStatement stmt = null;

ResultSet rs = null;//数据集

try {

conn = DBHelper.getConnection();

String sql = "select * from  hero where id=?;";//Sql语句

stmt = conn.prepareStatement(sql);//创建连接对象

stmt.setString(1, id);  //这里指定这个stmt要接收一个string类型的参数

// 也可以指定stmt.setInt(1,id);   指定stmt要接收一个int类型的参数数，意思是编号从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 {

//释放数据集对象

if(rs!=null) {

try {

rs.close();

rs=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

//释放语句对象

if(stmt!=null) {

try {

stmt.close();

stmt=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

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

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

//根据年份获取英雄信息

public ArrayList<Hero> GetHerosByYear(String year) {

Connection conn=null;

PreparedStatement stmt = null;

ResultSet rs = null;//数据集

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);//创建连接对象

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 {

//释放数据集对象

if(rs!=null) {

try {

rs.close();

rs=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

//释放语句对象

if(stmt!=null) {

try {

stmt.close();

stmt=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

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

}

下面的是写入（出现了问题）：

1，查询的时候，用的方法是：   rs = stmt.executeQuery();  但是写入的时候，需要用到方法是：stmt.execute();

2，在设置sql语句的时候，一直出错 

Unknown column 'xxx' in 'field list'  。

但是如果我直接从mysql中复制语句过来，比如：

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

就没有问题。纠结死我了。直到后来，发现，写入的内容需要用 单引号包起来。于是，我改成了：（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;

//保存数据内容进入到数据库中去

public class DanMuDao {

public Boolean SaveDanMu(String content) {

Connection conn=null;

PreparedStatement stmt = null;

ResultSet rs = null;//数据集

try {

conn = DBHelper.getConnection();

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

String sql="insert INTO danmu(content) values ('"+content+"')";

stmt = conn.prepareStatement(sql);//创建连接对象

stmt.execute();

}catch(Exception ex) {

ex.printStackTrace();

return false;

}finally {

//释放数据集对象

if(rs!=null) {

try {

rs.close();

rs=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

//释放语句对象

if(stmt!=null) {

try {

stmt.close();

stmt=null;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return false;

}

}