怕自己以后忘了,特意写下来,自己配置的用myeclipse连接mysql的步骤
1、首先下载一个用于连接mysql的连接驱动,下载地址链接: https://pan.baidu.com/s/14v_Aujd1kBw3ggIx0Qn-EA 密码: 7ida
2、新建一个数据库的连接Connection,如下实例
//连接数据库的详细信息
package model;
import java.sql.*;
public class ConnDB {
private Connection conn=null;
private static String url="jdbc:mysql://localhost:3306/userinfo?useSSL=false";
//userinfo为你自己需要连接的数据库的名称 这个为固定格式
private static String name="root";
//root 为数据库登录的用户名
private static String password="xy818514";
//xy818514为此用户的登录密码
public Connection getconn(){
try {
//加载驱动程序;
Class.forName("com.mysql.jdbc.Driver");
//获得数据库连接;
conn=DriverManager.getConnection(url,name,password);
//通过数据库的连接操作数据库,实现增删改查。
//创建一个 Statement 对象来将 SQL 语句发送到数据库
// Statement stmt=conn.createStatement();
// ResultSet rs=stmt.executeQuery("select * from user;");
}catch(ClassNotFoundException e){
//一定要打印异常
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
return conn;
}
}
然后,在另外一个模型中,对web需要的数据,建立相应的数据查询,修改,插入等操作,如下实例
//这是一个处理类,有人把它叫做bo,主要封装对user表的操作,
//主要为增删修改查;
package model;
import model.Userbean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
/**
*
* @author Administrator
*
*/
public class Userbeancl {
private Statement stmt = null;
private ResultSet res = null;
private Connection conn = null;
private int pagecount=0;
private int pagesize=3; //自己定义
//int pagenow=1; //用户选择
private int rowcount=0;
//精确或模糊查询用户信息
public ArrayList<Userbean> getuser(String u,String mod){
//
ArrayList<Userbean> relist = new ArrayList<Userbean>();
try {
conn = new ConnDB().getconn();
//得到连接
stmt = conn.createStatement();
//建立查询
if(mod.equals("equal")){
res = stmt.executeQuery("SELECT userid,username,sex,tel,bobby,banji FROM USER where username ='"+u+"'");
}else{
//res = stmt.executeQuery("SELECT userid,username,sex,tel,bobby,banji FROM USER where username like "+"%"+u+"%");
res = stmt.executeQuery("SELECT userid,username,sex,tel,bobby,banji FROM USER where username like '"+"%"+u+"%"+"'");
}
// Userbean ub = new Userbean(); 放在外面 会是重复的值
while(res.next()){
Userbean ub = new Userbean();
ub.setUserid(res.getInt(1));
ub.setUsername(res.getString(2));
ub.setUserpwd(res.getString(3));
ub.setTel(res.getString(4));
ub.setSex(res.getString(5));
ub.setHobby(res.getString(6));
relist.add(ub);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
close();
}
return relist;
}
//返回总的页数
public int getpagecount(){
conn = new ConnDB().getconn();
try {
//得到连接
stmt = conn.createStatement();
//建立具体的查询语句
res = stmt.executeQuery("select count(*) from user");
//得到此查询的总数,用于分页page
if(res.next()){
rowcount = res.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
int temp = rowcount%pagesize;
int x = rowcount/pagesize;
int r = (temp==0)?x:x+1;
return r;
}
//添加用户
public boolean adddata(String username,String userpwd,String sex,String tel){
//
//具体要插入字段的 执行语句
String str = ("INSERT INTO USER (username,userpwd,sex,tel) VALUES ("+'"'+username+'"'+","+'"'+userpwd+'"'+","+'"'+sex+'"'+","+'"'+tel+'"'+")");
boolean flag = false;
try {
conn = new ConnDB().getconn();
//得到连接
stmt = conn.createStatement();
//建立插入操作
String sqlstr = new String(str.getBytes("iso-8859-1"),"UTF-8");
//System.out.println(sqlstr);
int a = stmt.executeUpdate(sqlstr);
//得到总数
if(a==1){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
close();
}
return flag;
}
//删除用户
public boolean deldata(int userid){
//
boolean flag = false;
try {
conn = new ConnDB().getconn();
//得到连接
stmt = conn.createStatement();
//建立删除操作
int a = stmt.executeUpdate("delete from user where userid = "+userid+"");
//stmt.executeUpdate返回的是受影响的行数,此处为删除一行,返回1
if(a==1){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
close();
}
return flag;
}
//创建返回的数据库集
public ArrayList<Userbean> getdata(int pagenow){
//
ArrayList<Userbean> relist = new ArrayList<Userbean>();
try {
conn = new ConnDB().getconn();
//得到连接
stmt = conn.createStatement();
//建立查询
res = stmt.executeQuery("SELECT userid,username,sex,tel,bobby,banji FROM USER limit "+pagesize*(pagenow-1)+","+pagesize);
// Userbean ub = new Userbean(); 放在外面 会是重复的值
while(res.next()){
Userbean ub = new Userbean();
ub.setUserid(res.getInt(1));
ub.setUsername(res.getString(2));
ub.setUserpwd(res.getString(3));
ub.setTel(res.getString(4));
ub.setSex(res.getString(5));
ub.setHobby(res.getString(6));
relist.add(ub);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
close();
}
return relist;
}
//创建close()方法
public void close(){
try {
if(res!=null){
res.close();
}
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
//创建checkuser方法
public boolean checkuser(String u,String p) {
//定义标志
boolean s = false;
//创建一个连接
conn=new ConnDB().getconn();
try {
stmt = conn.createStatement();
res = stmt.executeQuery("select userpwd from user where username ='"+u+"'");
if(res.next()){
if(res.getString(1).equals(p)){
s=true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
//关闭各种资源
close();
}
return s;
}
}
后面在servlet中,根据表单的具体响应事件,执行对应的动态响应方法,给出不同的结果,如下实例
package controller;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import model.Userbean;
import model.Userbeancl;
public class Usercl_servlet extends HttpServlet {
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String flag = request.getParameter("flag");
if(flag.equals("fenye"))
{
try {
int pagenow= Integer.parseInt(request.getParameter("pagenow"));
//得到用户定义的pagenow 及所需数据
Userbeancl ubweb = new Userbeancl();
ArrayList<Userbean> relist = ubweb.getdata(pagenow);
int pagecount = ubweb.getpagecount();
//传递数据给web_s.jsp页面
request.setAttribute("result", relist);
request.setAttribute("pagecount", pagecount+"");
request.getRequestDispatcher("/web_s.jsp").forward(request, response);
} catch (Exception e) {
e.printStackTrace();
}
}
else if(flag.equals("deluser")){
int userid = Integer.parseInt(request.getParameter("userid"));
try {
int pagenow= Integer.parseInt(request.getParameter("pagenow"));
//得到用户定义的pagenow 及所需数据
Userbeancl ubweb = new Userbeancl();
if(ubweb.deldata(userid)){
//删除成功
request.getRequestDispatcher("/suc_s.jsp").forward(request, response);
}
else{
//删除失败
request.getRequestDispatcher("/def_s.jsp").forward(request, response);
}
//传递数据给web_s.jsp页面
} catch (Exception e) {
e.printStackTrace();
}
}
else if(flag.equals("adduser")){
try {
Userbeancl ubweb = new Userbeancl();
String username = request.getParameter("username");
String userpwd = request.getParameter("userpwd");
String sex = request.getParameter("sex");
String tel = request.getParameter("tel");
if(ubweb.adddata(username,userpwd,sex,tel)){
//添加成功
request.getRequestDispatcher("/suc_s.jsp").forward(request, response);
}else{
//添加失败
request.getRequestDispatcher("/def_s.jsp").forward(request, response);
}
} catch (Exception e) {
e.printStackTrace();
}
}
else if(flag.equals("chaxun")){
try {
Userbeancl ubweb = new Userbeancl();
String mod = request.getParameter("mod");
String username = request.getParameter("username");
ArrayList<Userbean> relist = ubweb.getuser(username, mod);
int pagecount = 1;
//传递数据给web_s.jsp页面
request.setAttribute("result", relist);
request.setAttribute("pagecount", pagecount+"");
request.getRequestDispatcher("/web_s.jsp").forward(request, response);
} catch (Exception e) {
e.printStackTrace();
}
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
}