MySql 执行JDBC联接（增/删/改/查）操作，mysqljdbc

MySql 执行JDBC联接（增/删/改/查）操作，mysqljdbc

视频地址：http://www.tudou.com/programs/view/4GIENz1qdp0/

新建BaseDao

package cn.wingfly.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BaseDao {
	Connection con = null;
	Statement st = null;
	ResultSet rs = null;

	/**
	 * 获得联接
	 * 
	 * @return
	 */
	public Connection getConnection() {
		try {
			// 加载驱动，这一句也可写为：Class.forName("com.mysql.jdbc.Driver"); 
			Class.forName("com.mysql.jdbc.Driver").newInstance();

			// 建立到MySQL的连接 
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/money_note?characterEncoding=UTF-8", "root", "root");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	/**
	 * 关闭数据源
	 */
	public void CloseConnection(Connection con, Statement s, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (s != null) {
				s.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package cn.wingfly.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Test extends BaseDao {

	Connection con = null;
	Statement st = null;
	ResultSet rs = null;

	/**
	 * 查询数据
	 */
	public void find() {
		con = getConnection();	// 获得联接
		try {
			st = con.createStatement();
			rs = st.executeQuery("select * from app_user");
			while (rs.next()) {
				System.out.println("编号：" + rs.getInt("uuid") + ", 姓名：" + rs.getString("userName") + ", 性别：" + rs.getString("sex") + ", 生日：" + rs.getString("birthday") + ", 住址：" + rs.getString("address"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			CloseConnection(con, st, rs);	// 关闭联接
		}
	}

	/**
	 * 添加数据
	 */
	public void add() {
		con = getConnection();
		try {
			st = con.createStatement();
			int result = st.executeUpdate("insert into app_user(userName,passWord,sex,birthday,address) values('赵丽颖','wanying','女','1992-02-03','北京市')");
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}
		} catch (SQLException e) {
			System.out.println("插入失败");
			e.printStackTrace();
		} finally {
			CloseConnection(con, st, rs);
		}
	}

	/**
	 * 更新数据
	 */
	public void update() {
		con = getConnection();
		try {
			st = con.createStatement();
			int result = st.executeUpdate("update app_user set address = '河南' where uuid = '3'");
			if (result > 0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			CloseConnection(con, st, rs);
		}
	}

	/**
	 * 删除数据
	 */
	public void delete() {
		con = getConnection();
		try {
			st = con.createStatement();
			int result = st.executeUpdate("delete from app_user where uuid = '3'");
			if (result > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			CloseConnection(con, st, rs);
		}
	}

	public static void main(String[] args) {
		Test test = new Test();
		//test.add();
		// test.update();
		 test.delete();
		test.find();

	}

}

/**
* 查找数据库总共有多少条记录
*/
public int findAllEmailCount() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select count(*) from email";
int count = 0;
try {
conn = DBHelp.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()){
count = rs.getInt(1);
}
} catch (Exception e) {
throw new RuntimeException("查找数据库出错");
} finally {
DBHelp.closeAll(null, pstmt, conn);
}
return count;
}
 

1.增加
String s1="insert into tableNames (id,name,password) values(myseq.nextval,?,?);"
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,dbUser,dbPwd);
PreparedStatement prepStmt = conn.prepareStatement(s1);
prepStmt.setString(1,name);
prepStmt.setString(2,password);
ResultSet rs=stmt.executeUpdate();
2、删除
String s2="delete from tbNames where name=?";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,dbUser,dbPwd);
PreparedStatement prepStmt = conn.prepareStatement(s2);
prepStmt.setString(1,name);
ResultSet rs=stmt.executeUpdate();
3、修改
String s3=“update tbNames set name=? where id=?”;
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,dbUser,dbPwd);
PreparedStatement prepStmt = conn.prepareStatement(s3);
prepStmt.setString(1,name);
prepStmt.setString(2,id);
ResultSet rs=stmt.executeUpdate();
4、查询
String s4="select id,name,password from tbNames";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,dbUser,dbPwd);
Statement stmt=conn.createStatement();
ResultSet rs = stmt.executeQuery(s4);
while(rs.next){
int id=rs.getInt(1);
String name = rs.getString(2);
String pwd=rs.getString(3);
System.out.println(id+name+pwd); }

以上四步必须都得关闭连接；！！！
rs.close();
stmt.close();
conn.close();...余下全文>>