B/S架构:Browser/Server
C/S架构:Client/Server
Tomcat控制台中文乱码问题,解决办法
1.打开你安装Tomcat的所在目录
2. 打开后选择conf目录。
将里面的logging.properties文件用编辑器打开,
打开后在50行左右找到
java.util.logging.ConsoleHandler.encoding = UTF-8
注释掉,修改为j
ava.util.logging.ConsoleHandler.encoding = GBK;
重新启动Tomcat就可以了。
SQL注入:
解决方法:不使用statement而使用preparedStatement
BaseDao:
package cn.kgc.kb09.dao;
import java.sql.*;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 11:09
* @Description:
**/
public class BaseDao {
public Connection getConn() throws Exception {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//驱动管理器来获取连接
String url="jdbc:mysql://192.168.237.100:3306/MySchool";
Connection conn= DriverManager.getConnection(url,"root","ok");
return conn;
}
/*public void showTables() throws Exception{
//获取连接
Connection conn=getConn();
//1、通过连接,先获取statement对象
Statement stmt=conn.createStatement();
//2、sql语句
String sql="show tables";//定义好sql语句
ResultSet rs = stmt.executeQuery(sql);//获取查询语句的结果集
while(rs.next()){//对结果集遍历
//好处:列名可以直接指定
System.out.println(rs.getString("Tables_in_MySchool"));
}
}*/
public ResultSet query(String sql,int id,String name) throws Exception{
//获取连接
Connection conn=getConn();
//1、通过连接,先获取statement对象
/*Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);*/
PreparedStatement pst=conn.prepareStatement(sql);
pst.setObject(1,id);
pst.setObject(2,name);
ResultSet rs = pst.executeQuery();
return rs;
}
public int update(String sql) throws Exception{
Connection conn=getConn();
Statement stmt=conn.createStatement();
int num = stmt.executeUpdate(sql);
return num;
}
public void close(Connection conn,Statement stmt,ResultSet rs) throws Exception{
if(conn!=null){
conn.close();
}
if(stmt!=null){
stmt.close();
}
if(rs!=null){
rs.close();
}
}
}
Student:
package cn.kgc.kb09.entity;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 11:50
* @Description:
**/
public class Student {
private int stu_id;
private String stu_name;
private String gender;
private String phone;
private String address;
private String email;
private String identityCard;
@Override
public String toString() {
return "Student{" +
"stu_id=" + stu_id +
", stu_name='" + stu_name + '\'' +
", gender='" + gender + '\'' +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", email='" + email + '\'' +
", identityCard='" + identityCard + '\'' +
'}';
}
public int getStu_id() {
return stu_id;
}
public void setStu_id(int stu_id) {
this.stu_id = stu_id;
}
public String getStu_name() {
return stu_name;
}
public void setStu_name(String stu_name) {
this.stu_name = stu_name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getIdentityCard() {
return identityCard;
}
public void setIdentityCard(String identityCard) {
this.identityCard = identityCard;
}
}
BaseDaoTest:
package cn.kgc.kb09.dao;
import cn.kgc.kb09.entity.Student;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import static org.junit.Assert.*;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 11:24
* @Description:
**/
public class BaseDaoTest {
@Test
public void getConn() throws Exception {
BaseDao baseDao = new BaseDao();
Connection conn = baseDao.getConn();
System.out.println(conn);
}
@Test
public void showTables() throws Exception{
BaseDao baseDao = new BaseDao();
//baseDao.showTables();
}
@Test
public void query() throws Exception{
BaseDao baseDao = new BaseDao();
String sql="select * from student where stu_id=? or stu_name like ?";
ResultSet rs=baseDao.query(sql,1014,"李%");
ArrayList<Student> stuList=new ArrayList<>();
while (rs.next()){
Student s=new Student();
s.setStu_id(rs.getInt("stu_id"));
s.setStu_name(rs.getString("stu_name"));
s.setPhone(rs.getString("phone"));
s.setIdentityCard(rs.getString("identityCard"));
s.setGender(rs.getString("gender"));
s.setEmail(rs.getString("email"));
s.setAddress(rs.getString("Address"));
stuList.add(s);
}
for (Student student : stuList) {
System.out.println(student);
}
baseDao.close(null,null,rs);
}
@Test
public void update() throws Exception{
BaseDao baseDao = new BaseDao();
String sql="insert into student(stu_name,gender,grade_id,phone)"
+" values('一灯','男',4,'13800009999'),('瑛姑','女',3,'13800008888')";
int num=baseDao.update(sql);
System.out.println(num > 0 ? "插入成功" : "插入失败");
}
//使用jdbc创建一个库,同时切换到这个库,创建一个表
@Test
public void testDo() throws Exception{
BaseDao baseDao = new BaseDao();
Connection conn = baseDao.getConn();
Statement stmt = conn.createStatement();
String createDatabase="create database if not exists userControl";
stmt.execute(createDatabase);
String changeDatabase="use userControl";
stmt.execute(changeDatabase);
String createTalbe ="create table if not exists user_info "
+" (uid int(11) auto_increment primary key,"
+"uname varchar(30),password varchar(30))";
stmt.execute(createTalbe);
}
}
PstDao:
package cn.kgc.kb09.dao;
import cn.kgc.kb09.util.Prop;
import java.sql.*;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 13:52
* @Description:
**/
public class PstDao {
private static String driver= Prop.getP("driver");
private static String url=Prop.getP("url");
private static String user=Prop.getP("user");
private static String pwd=Prop.getP("pwd");
public static Connection getConn(){
try {
Class.forName(driver);
return DriverManager.getConnection(url,user,pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn, PreparedStatement pst, ResultSet rs){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static ResultSet query(String sql,Object ...params){
Connection conn = getConn();
PreparedStatement pst=null;
ResultSet rs=null;
try {
pst=conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1,params[i]);
}
rs = pst.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//close(conn,pst,null);
}
return rs;
}
public static int update(String sql,Object... params){
Connection conn = getConn();
PreparedStatement pst=null;
try {
pst = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1,params[i]);
}
return pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return -1;
}
}
PstDaoTest:
package cn.kgc.kb09.dao;
import org.junit.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 14:07
* @Description:
**/
public class PstDaoTest {
@Test
public void query() throws SQLException {
String sql="select subjectName,score from subject s left join result on sub_no=subjectno where score>? and subjectname in(?,?)";
ResultSet rs=PstDao.query(sql,"60","高等数学-1","高等数学-2");
while(rs.next()){
System.out.print(rs.getString("subjectname")+"----------");
System.out.println(rs.getInt("score"));
}
}
@Test
public void update(){
String sql="update account set cash=cash+? where name=?";
int num=PstDao.update(sql,10000,"ICBC");
System.out.println(num > 0 ? "更新成功" : "更新失败");
}
}
db.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.237.100:3306/kgcnews
user=root
pwd=ok
Prop:
package cn.kgc.kb09.util;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 15:27
* @Description:
**/
public class Prop {
static Properties p=new Properties();
public static String getP(String param){
try {
p.load(new FileInputStream("resources/db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
return p.getProperty(param);
}
}
NewsDao:
package cn.kgc.kb09.dao;
import cn.kgc.kb09.util.Prop;
import java.sql.*;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 16:03
* @Description:
**/
public class NewsDao {
public static Connection getConn(){
try {
Class.forName(Prop.getP("driver"));
return DriverManager.getConnection(Prop.getP("url"),Prop.getP("user"),Prop.getP("pwd"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public int updateNews(String sql,Object... param){
Connection conn = NewsDao.getConn();
try {
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
pst.setObject(i+1,param[i]);
}
return pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
public ResultSet selectNews(String sql,Object... param){
Connection conn = NewsDao.getConn();
ResultSet rs=null;
try {
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
pst.setObject(i+1,param[i]);
}
return rs= pst.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
}
NewsDaoTest:
package cn.kgc.kb09.dao;
import org.junit.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Author: ChaoKeAiMuZhi
* @Date: 2020/8/21 16:10
* @Description:
**/
public class NewsDaoTest {
@Test
public void testInsert(){
NewsDao newsDao = new NewsDao();
String sql="insert into news_detail (categoryId,title,summary,content) values (?,?,?,?)";
int i = newsDao.updateNews(sql, 2, "假如特朗普是一门编程语言-Ctrump", "Make C++ Great Again", "写字楼里写字间,写字间里程序员,开天辟地Ctrump,一切旧貌换新颜。");
System.out.println(i > 0 ? "插入成功" : "插入失败");
}
@Test
public void testUpdate(){
NewsDao newsDao = new NewsDao();
String sql="update news_detail set title=? where id=?";
int i = newsDao.updateNews(sql, "jdbc真好玩", 1);
System.out.println(i > 0 ? "修改成功" : "修改失败");
}
@Test
public void testDelete(){
NewsDao newsDao = new NewsDao();
String sql="delete from news_detail where id=?";
int i = newsDao.updateNews(sql, 1);
System.out.println(i > 0 ? "删除成功" : "删除失败");
}
@Test
public void testSelect() throws SQLException {
NewsDao newsDao = new NewsDao();
String sql="select * from news_detail where id>? and author=?";
ResultSet rs = newsDao.selectNews(sql, 1, "admin");
while(rs.next()){
System.out.println(rs.getString("title"));
System.out.println(rs.getInt("id"));
}
}
}