100分 求连接数据库 实现增删改查;以学生表为例, 连接oracle数据库。 学生表(students)已经建好,(Sid CHAR(4) PRIMARY KEY,Sname VERCHAR(20),Sno VERCHAR(5),
Ssex VERCHAR(4),Sage NUMBER,Sweight NUMBER,Sdept VERCHAR(20),Sentrance VERCHAR(20),
Saddress VERCHAR(20),Sphone VERCHAR(10));一共有10个属性;能帮做student最好;有相似列子发给我也行;邮箱 [email protected]越快越好 有参考价值的就给分,顶贴也有份。谢谢了
Ssex VERCHAR(4),Sage NUMBER,Sweight NUMBER,Sdept VERCHAR(20),Sentrance VERCHAR(20),
Saddress VERCHAR(20),Sphone VERCHAR(10));一共有10个属性;能帮做student最好;有相似列子发给我也行;邮箱 [email protected]越快越好 有参考价值的就给分,顶贴也有份。谢谢了
解决方案 »
- 怎么看待高校老师所做的科研项目,发表的科研论文和实际软件公司所开发的项目
- 各位帮小弟看看我这道servlet程序为什么不能够运行!
- struts配置相同actionform的scope不同会如何?
- 如何实现页面加载时表单自动提交,且只提交一次
- 调用webservice结果传回的资料不全……
- 请问,为什么session会自动为空?我也没有关闭IE,用的容器是Tomcat4.0?
- 请教如何在idea中启动和关闭tomcat5.0.28,idea版本为4.5.4,多谢
- 我想把application转换成applet!但我是菜菜鸟,能帮帮我吗??
- 定时任务扫描数据库表数据下发邮件
- java代码中执行包含count()函数的执行问题
- 公司的java项目把99.5%以上的业务逻辑都写在Action里面,太杯具了
- 如何用HttpUrlConnecton进行post的提交?
package com.coderdream.jdbc.oracle;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JdbcExample { private static Connection getConn() { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@你的主机ip :1521:orcl";// 设置连接字符串 String username = "scott";//用户名 String password = "tiger";//密码 Connection conn = null; //创建数据库连接对象 try { Class.forName(driver); // new oracle.jdbc.driver.OracleDriver(); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } private static int insert(String username, String password) { Connection conn = getConn(); int i = 0; String sql = "insert into users (username,password) values(?,?)"; //增 PreparedStatement pstmt; // 建立一个查询对象 try { pstmt = conn.prepareStatement(sql); // Statement stat = conn.createStatement(); pstmt.setString(1, username); pstmt.setString(2, password); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; } private static void query() { Connection conn = getConn(); String sql = "select * from users"; //查 PreparedStatement pstmt; try { pstmt = conn.prepareStatement(sql); // 建立一个结果集,用来保存查询出来的结果 ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println("name: " + rs.getString("username") + " \tpassword: " + rs.getString("password")); } rs.close(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } private static int update(String oldName, String newPass) { //改 Connection conn = getConn(); int i = 0; String sql = "update users set password='" + newPass + "' where username='" + oldName + "'"; PreparedStatement pstmt; try { pstmt = conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; } private static int delete(String username) { Connection conn = getConn(); int i = 0; String sql = "delete users where username='" + username + "'"; //删 PreparedStatement pstmt; try { pstmt = conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; } //主方法,进行测试 public static void main(String[] args) { insert("test", "123"); insert("admin", "456"); query(); update("admin", "456"); query(); delete("admin"); query();
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;import com.po.User;public class UserDAO {
private Connection conn = null;
private Statement sta = null;
private ResultSet rs = null;
//接连数据库
public Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String name = "root";
String password = "123";
String sql = "jdbc:mysql://localhost:3306/user";
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(sql, name, password);
return conn;
}
//查询数据库是否有前台的信息
public boolean isokUser(User user) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "select * from user where NAME='"+user.getName()+"' and PASSWORD='"+user.getPassword()+"'";
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
boolean isok = false;
isok = rs.first();
return isok;
}
//查询所有数据库
public List allUser() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "select * from user";
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
List list = new ArrayList();
while(rs.next()){
User user = new User();
user.setId(Integer.parseInt(rs.getString("ID")));
user.setName(rs.getString("NAME"));
user.setPassword(rs.getString("PASSWORD"));
list.add(user);
}
return list;
}
//增加用户
public void insertUser(User user) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "insert into user(NAME,PASSWORD) values('"+user.getName()+"','"+user.getPassword()+"')";
conn = this.getConnection();
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.close();
conn.close();
}
//查询用户
public List selectID(User user) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "select * from user where ID='"+user.getId()+"'";
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
List list = new ArrayList();
while(rs.next()){
User user1 = new User();
user.setId(Integer.parseInt(rs.getString("ID")));
user.setName(rs.getString("NAME"));
user.setPassword(rs.getString("PASSWORD"));
list.add(user);
}
return list;
}
//以ID查询编辑
public User searchID(int id) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "select * from user where ID='"+id+"'";
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
User user = new User();
while(rs.next()){
user.setId(Integer.parseInt(rs.getString("ID")));
user.setName(rs.getString("NAME"));
user.setPassword(rs.getString("PASSWORD"));
}
return user;
}
//编辑用户
public void updateUser(User user) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "update user set NAME='"+user.getName()+"',PASSWORD='"+user.getPassword()+"' where ID='"+user.getId()+"'";
conn = this.getConnection();
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.close();
conn.close();
}
//删除用户
public void deleteUser(int id) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "delete from user where ID='"+id+"'";
conn = this.getConnection();
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.close();
conn.close();
}
}
package com.dao;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;import com.po.PageAction;
import com.po.User;public class UserDAO {
private Connection conn = null;
private Statement sta = null;
private ResultSet rs = null;
private int DBsum = 0;//总记录数
private int sumpage = 0;//总页数
//连接数据库的方法
public Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String name = "root";
String password = "123";
String url = "jdbc:mysql://localhost:3306/user";
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url, name, password);
return conn;
}
//查询数据库是否有前台的用户
public boolean isokUser(User user) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "select * from user where NAME='"+user.getName()+"' and PASSWORD='"+user.getPassword()+"'";
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
boolean isok = false;
isok = rs.first();
return isok;
}
//总页数
public Integer getRow(PageAction pa) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = pa.getSql();
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
rs.last();//将数据库游标移到最后一条记录
DBsum = rs.getRow();//返回当前数据库游标指向的位置(获得数据库的总记录数)
sumpage = (DBsum+pa.getSize()-1)/pa.getSize();//计算有多少个页面
return sumpage;
}
//取得页面的记录
public List getDB(PageAction pa) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = pa.getSql();
int page = pa.getPage();//取得当前页面数
int size = pa.getSize();//取得页面显示的数量
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
int sumpage = this.getRow(pa);//取得总页数
if(page<=0){
page = 1;
}
if(page>sumpage){
page = sumpage;
}
if(sumpage>0){
rs.absolute((page-1)*size+1);//游标移到第几条记录
}
List list = new ArrayList();
int i = 0;
//从游标的下标开始循环出size条记录
while(i<size&&!rs.isAfterLast()){
User user = new User();
user.setId(Integer.parseInt(rs.getString("ID")));
user.setName(rs.getString("NAME"));
user.setPassword(rs.getString("PASSWORD"));
list.add(user);
rs.next();
i++;
}
return list;
}
//插入数据
public void newUser(User user) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "insert into user(NAME,PASSWORD) values('"+user.getName()+"','"+user.getPassword()+"')";
conn = this.getConnection();
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.close();
conn.close();
}
//以ID查询编辑
public User searchID(int id) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "select * from user where ID='"+id+"'";
conn = this.getConnection();
sta = conn.createStatement();
rs = sta.executeQuery(sql);
User user = new User();
while(rs.next()){
user.setId(Integer.parseInt(rs.getString("ID")));
user.setName(rs.getString("NAME"));
user.setPassword(rs.getString("PASSWORD"));
}
return user;
}
//修改用户
public void update(User user) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "update user set NAME='"+user.getName()+"',PASSWORD='"+user.getPassword()+"' where ID='"+user.getId()+"'";
conn = this.getConnection();
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.close();
conn.close();
}
//删除用户
public void deleteUser(int id) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
String sql = "delete from user where ID='"+id+"'";
conn = this.getConnection();
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.close();
sta.close();
}
}
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
con = DriverManager.getConnection (“jdbc:oracle:thin:@192.168.96.1:1521:oracle9i”,user,password);
}
catch (SQLException e) {
return null;
}
return con;
} package com.kingson.db;import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;public class DBConnectionManager {
static private DBConnectionManager instance; // 唯一实例
static private int clients;private Vector drivers = new Vector();
private PrintWriter log;
private Hashtable pools = new Hashtable();/**
* 返回唯一实例.如果是第一次调用此方法,则创建实例
*
* @return DBConnectionManager 唯一实例
*/
static synchronized public DBConnectionManager getInstance() {
if (instance == null) {
instance = new DBConnectionManager();
}
clients++;
return instance;
}/**
* 建构函数私有以防止其它对象创建本类实例
*/
private DBConnectionManager() {
init();
}/**
* 将连接对象返回给由名字指定的连接池
*
* @param name 在属性文件中定义的连接池名字
* @param con 连接对象
*/
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
pool.freeConnection(con);
}
}/**
* 获得一个可用的(空闲的)连接.如果没有可用连接,且已有连接数小于最大连接数
* 限制,则创建并返回新连接
*
* @param name 在属性文件中定义的连接池名字
* @return Connection 可用连接或null
*/
public Connection getConnection(String name) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection();
}
return null;
}/**
* 获得一个可用连接.若没有可用连接,且已有连接数小于最大连接数限制,
* 则创建并返回新连接.否则,在指定的时间内等待其它线程释放连接.
*
* @param name 连接池名字
* @param time 以毫秒计的等待时间
* @return Connection 可用连接或null
*/
public Connection getConnection(String name, long time) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection(time);
}
return null;
}/**
* 关闭所有连接,撤销驱动程序的注册
*/
public synchronized void release() {
// 等待直到最后一个客户程序调用
if (--clients != 0) {
return;
}Enumeration allPools = pools.elements();
while (allPools.hasMoreElements()) {
DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
pool.release();
}
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElements()) {
Driver driver = (Driver) allDrivers.nextElement();
try {
DriverManager.deregisterDriver(driver);
log("撤销JDBC驱动程序 " + driver.getClass().getName()+"的注册");
}
catch (SQLException e) {
log(e, "无法撤销下列JDBC驱动程序的注册: " + driver.getClass().getName());
}
}
}/**
* 根据指定属性创建连接池实例.
*
* @param props 连接池属性
*/
private void createPools(Properties props) {
Enumeration propNames = props.propertyNames();
while (propNames.hasMoreElements()) {
String name = (String) propNames.nextElement();
if (name.endsWith(".url")) {
String poolName = name.substring(0, name.lastIndexOf("."));
String url = props.getProperty(poolName + ".url");
if (url == null) {
log("没有为连接池" + poolName + "指定URL");
continue;
}
String user = props.getProperty(poolName + ".user");
String password = props.getProperty(poolName + ".password");
String dbip = props.getProperty(poolName + ".db_ip", "192.168.96.1");
String dbport = props.getProperty(poolName + ".db_port", "1521");
String dbuid = props.getProperty(poolName + ".db_uid", "ORACLE9I");
String maxconn = props.getProperty(poolName + ".maxconn", "0");
//连接信息
String dbInfo = user + "/" + password + "@" + dbip + ":" + dbport + ":" + dbuid;
int max;
try {
max = Integer.valueOf(maxconn).intValue();
}
catch (NumberFormatException e) {
log("错误的最大连接数限制: " + maxconn + " .连接池: " + poolName);
max = 0;
}
DBConnectionPool pool = new DBConnectionPool(poolName, url,dbInfo, max);
pools.put(poolName, pool);
log("成功创建连接池" + poolName);
}
}
}