week 3.9-3.15
- | Study_update |
---|---|
-Mon | 异常抛出 |
-Tue | 数据结构-二叉树学习,JDBC编程 |
-Wes | SQL注入 |
-Thus | 分页查询,插入数据 |
-Fri | 事务,连接池 |
-Sat | C3p0,dbcp |
-Sun | - |
3.10 Tuesday
二叉树性质1:二叉树的第i层上最多有2的i-1次方个节点。
二叉树性质2:高度(或深度)为i的二叉树最多有2的i次方-1个节点
二叉树性质3:度为2的节点数目等于度为0的节点数目-1
二叉树性质4:在完全二叉树中,节点数等于n,层数等于log以2为底n向下取整+1
JDBC
导入Mysql jar包,建立与数据库连接,执行查询语句
/**
* @author lzr
* @date 2020/3/10 17:32:58
* @description
*/
public class JDBC_Demo01 {
public static void main(String[] args) {
ResultSet rs=null;
Statement stmt=null;
Connection con=null;
try {
Class.forName("com.mysql.jdbc.Driver");//使用驱动连接数据库
String url="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8";
String user="root";
String password="root";
con = DriverManager.getConnection(url,user,password);//建立连接
stmt = con.createStatement();
String sql="select * from user";
rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
3.11 wednesday
/**
* @author lzr
* @date 2020/3/10 17:32:58
* @description
*/
public class JDBC_Demo01 {
public static void main(String[] args) {
System.out.println(selectByUsernamePassword("jack","whatever"));
System.out.println(selectByUP("jack","whatever"));
}
public static void selectAll(){
ResultSet rs=null;
Statement stmt=null;
Connection con=null;
try {
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false";
String user="root";
String password="root";
con = DriverManager.getConnection(url,user,password);//建立连接
stmt = con.createStatement();
String sql="select * from user";
rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
public static boolean selectByUsernamePassword(String username,String password){
//注册驱动
Connection con=null;
Statement stmt=null;
ResultSet res=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false";
con = DriverManager.getConnection(url,"root","root");
stmt = con.createStatement();
String sql="select * from user where username='"+username+"' and password='"+password+"'";
res = stmt.executeQuery(sql);
System.out.println(sql);
if(res.next()){
return true;
}else{
return false;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(con!=null)con.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt!=null)stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(res!=null)res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
public static boolean selectByUP(String username,String password){
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false";
con = DriverManager.getConnection(url,"root","root");
String sql="select * from user where username=? and password=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,password);
rs=pstmt.executeQuery();
if(rs.next()){
return true;
}else{
return false;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}return false;
}
}
3.12 Thursday
public static void selectByPage(int pageNum,int pageCnt){
String url="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false";
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url,"root","root");
String sql="select * from user limit ?,?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,(pageNum-1)*pageCnt);
pstmt.setInt(2,pageCnt);
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void insert(String username,String password){
Connection con=null;
PreparedStatement pstmt=null;
con=JDBCUtils.GetConnection("jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false","root","root");
try {
String sql="insert into user(username,password) values(?,?)";
pstmt=con.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,password);
int rs=pstmt.executeUpdate();//发起修改,返回值代表受到影响的行数.
System.out.println(rs);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.Close(null,pstmt,con);
}
}
3.13 Friday
转账
public static void transferAccounts(String username1,String username2,int money){
Connection con=null;
PreparedStatement pstmt=null;
PreparedStatement pstmt1=null;
con=JDBCUtils.GetConnection("jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false","root","root");
try {
con.setAutoCommit(false);//设置是否自动提交 ,开启事务
String sql="update user set balance=balance-? where username=?";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1,money);
pstmt.setString(2,username1);
int rs1=pstmt.executeUpdate();
System.out.println(rs1);
String sql1="update user set balance=balance+? where username=?";
pstmt1=con.prepareStatement(sql1);
pstmt1.setInt(1,money);
pstmt1.setString(2,username2);
int rs2=pstmt1.executeUpdate();
System.out.println(rs2);
con.commit();//提交事务
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.Close(pstmt1,pstmt,con);
}
}
3.14 Saturday
写自己的JDBC工具包
/**
* @author lzr
* @date 2020/3/12 14:51:41
* @description
*/
public class JDBCUtils {
private static final String URL="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false";
private static final String USERNAME="root";
private static final String PASSWORD="root";
private static ArrayList<Connection> conList=new ArrayList<Connection>();
static{
for(int i=0;i<5;i++){
Connection con=CreateConnection();
conList.add(con);
}
}
public static Connection GetConnection(){
if(conList!=null){
Connection con=conList.get(0);
conList.remove(con);
return con;
}else{
return CreateConnection();
}
}
public static Connection CreateConnection(){
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void Close(ResultSet rs, PreparedStatement pstmt,Connection con){
closeRs(rs);
closePstmt(pstmt);
closeCon(con);
}
public static void Close( PreparedStatement pstmt,Connection con){
closePstmt(pstmt);
closeCon(con);
}
public static void Close(PreparedStatement pstmt,PreparedStatement pstmt2,Connection con){
closePstmt(pstmt);
closePstmt(pstmt2);
closeCon(con);
}
private static void closeRs(ResultSet rs){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closePstmt(PreparedStatement pstmt){
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeCon(Connection con){
// try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
// }
conList.add(con);
}
}
c3P0的使用
/**
* @author lzr
* @date 2020/3/14 09:16:47
* @description
*/
public class C3P0 {
private static final String URL="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false";
private static final String USERNAME="root";
private static final String PASSWORD="root";
private static ComboPooledDataSource ds=null;
static{
try {
ds=new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl(URL);
ds.setUser(USERNAME);
ds.setPassword(PASSWORD);
ds.setInitialPoolSize(5);//最大初始
ds.setMaxPoolSize(20);//最大连接
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void Close(ResultSet rs, PreparedStatement pstmt, Connection con){
closeRs(rs);
closePstmt(pstmt);
closeCon(con);
}
public static void Close( PreparedStatement pstmt,Connection con){
closePstmt(pstmt);
closeCon(con);
}
public static void Close(PreparedStatement pstmt,PreparedStatement pstmt2,Connection con){
closePstmt(pstmt);
closePstmt(pstmt2);
closeCon(con);
}
private static void closeRs(ResultSet rs){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closePstmt(PreparedStatement pstmt){
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeCon(Connection con){
try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
DBPC的使用
/**
* @author lzr
* @date 2020/3/13 16:04:11
* @description
*/
public class DbcpDataSource {
private static final String URL="jdbc:mysql://localhost:3306/web01?userUnicode=true&charachterEncoding=UTF8&useSSL=false";
private static final String USERNAME="root";
private static final String PASSWORD="root";
private static BasicDataSource ds;
static {
//初始化dbcp数据源
ds=new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl(URL);
ds.setUsername(USERNAME);
ds.setPassword(PASSWORD);
ds.setInitialSize(5);//初始生成的连接数
ds.setMaxTotal(20);//最大连接数
ds.setMinIdle(3);//最小空闲数量
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void Close(ResultSet rs, PreparedStatement pstmt, Connection con){
closeRs(rs);
closePstmt(pstmt);
closeCon(con);
}
public static void Close( PreparedStatement pstmt,Connection con){
closePstmt(pstmt);
closeCon(con);
}
public static void Close(PreparedStatement pstmt,PreparedStatement pstmt2,Connection con){
closePstmt(pstmt);
closePstmt(pstmt2);
closeCon(con);
}
private static void closeRs(ResultSet rs){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closePstmt(PreparedStatement pstmt){
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeCon(Connection con){
try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}