|
Name |
English |
Math |
Computer |
|
zhangsan |
69 |
86 |
77 |
|
lisi |
55 |
100 |
88 |
根据上面给出的Student表，在MySQL数据库中完成如下操作：
（1） 在MySQL中创建Student表，并录入数据；
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(45) COLLATE utf8_bin NOT NULL,
`english` varchar(45) COLLATE utf8_bin NOT NULL,
`math` varchar(45) COLLATE utf8_bin NOT NULL,
`computer` varchar(45) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
（2） 用SQL语句输出Student表中的所有记录；
SELECT * FROM test_nosql.student;
（3） 查询zhangsan的Computer成绩；
SELECT computer FROM test_nosql.student where name_='zhangsan';
（4）修改lisi的Math成绩，改为95。
update test_nosql.student set math='95' where name_='lisi';
根据上面已经设计出的Student表，使用MySQL的JAVA客户端编程实现以下操作：
（1）向Student表中添加如下所示的一条记录：
|
scofield |
45 |
89 |
100 |
（2）获取scofield的English成绩信息
package utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCTools {
private static ComboPooledDataSource cpds;
static {
cpds=new ComboPooledDataSource("testc3p0");
}
public static Connection getConnection() throws SQLException
{
Connection conn=null;
conn= cpds.getConnection();
return conn;
}
public static void release(Connection conn,PreparedStatement ps,ResultSet rs)
{
try{
if(conn!=null)
{
conn.close();
}
if(ps!=null)
{
ps.close();
}
if(rs!=null)
{
rs.close();
}
}catch(SQLException e)
{
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException{
System.out.println(JDBCTools.getConnection());
}
}
package Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.JDBCTools;
public class BaseDao {
private QueryRunner qr=new QueryRunner();
private Connection conn=null;
private PreparedStatement ps=null;
private ResultSet rs=null;
//更新，删除，插入
public int upDate(String sql,Object ...
args)
{
try {
conn=JDBCTools.getConnection();
return qr.update(conn,sql,args);
} catch (SQLException e) {
// TODO Auto-generated catch
block
e.printStackTrace();
}finally{
JDBCTools.release(conn, ps, rs);
}
return -1;
}
//查询单个数据
public <T>T queryForOne(Class<T> type,String sql,Object ... args)
{
try {
conn=JDBCTools.getConnection();
return qr.query(conn,sql,new BeanHandler<T>(type),args);
} catch (SQLException e) {
// TODO Auto-generated catch
block
e.printStackTrace();
}finally{
JDBCTools.release(conn, ps, rs);
}
return null;
}
//查询list数据
public <T>List<T>
queryForList(Class<T> type,String sql,Object ... args)
{
try {
conn=JDBCTools.getConnection();
return qr.query(conn,sql,new BeanListHandler<T>(type),args);
} catch (SQLException e) {
// TODO Auto-generated catch
block
e.printStackTrace();
}finally{
JDBCTools.release(conn, ps, rs);
}
return null;
}
//将单个之封装如count（*）
public Object
queryForSingleValue(String sql,Object...args)
{
try {
conn=JDBCTools.getConnection();
return qr.query(conn,sql,new ScalarHandler(),args);
} catch (SQLException e) {
// TODO Auto-generated catch
block
e.printStackTrace();
}
return null;
}
}
package test;
import Dao.BaseDao;
import Data.ArticleData;
public class test_student_dao extends BaseDao {
public int qrInsert(String name_,String english ,String math,String computer )
{
String sql="INSERT INTO student(name_,english,math,computer) values(?,?,?,?)";
return upDate(sql,name_,english,math,computer);
}
public test_student qrFindOne()
{
String sql="select english from student where name_='scofield'";
return queryForOne(test_student.class,sql);
}
public static void main(String arg[]){
test_student_dao test=new test_student_dao();
test.qrInsert("scofield","45","89","100");
test_student student=new test_student();
student=test.qrFindOne();
System.out.printf("英语成绩："+student.getEnglish());
}
}