时间：2023-05-23 15:48:01 | 来源：网站运营
时间：2023-05-23 15:48:01 来源：网站运营
Java实战Tomcat+Servlet+Sql开发简单网站配置环境：
在线咨询
/* * 这里放的是 静态Sql代码*/public class SqlCode { // 在数据students中创建表scores static String createTable = "" + "USE students;" + "/n" + "CREATE TABLE scores" + "(" + "sno int not null," + "name varchar(20) not null," + "ssex varchar(10) CHECK(ssex IN('boy','girl'))," + "score int not null," + "PRIMARY KEY(sno)," + ")"; //在表格scores插入5条记录 static String insertValues = "" + "USE students" + "/n" + "INSERT INTO scores(sno,name,ssex,score) VALUES(1,'DaWang','boy','61')" + "/n" + "INSERT INTO scores(sno,name,ssex,score) VALUES(2,'ErWang','girl','62')" + "/n" + "INSERT INTO scores(sno,name,ssex,score) VALUES(3,'SanWang','boy','63')" + "/n" + "INSERT INTO scores(sno,name,ssex,score) VALUES(4,'siWang','girl','65')" + "/n" + "INSERT INTO scores(sno,name,ssex,score) VALUES(5,'wuWang','girl','66')"; //显示5位学生的所有信息 static String queryString = "" + "USE students" + "/n" + "SELECT TOP 5 * FROM scores"; //将三位男生的得分减去5 tucao:男生真累 static String updateScoreBoy = "" + "USE students" + "/n" + "UPDATE scores " + "/n" + "SET score = score - 5" + "/n" + "WHERE ssex = 'boy'" + "/n"; //将两位女生的成绩加上3分 static String updateScoreGirl = "" + "USE students" + "/n" + "UPDATE scores " + "/n" + "SET score = score + 3" + "/n" + "WHERE ssex = 'girl'" + "/n"; //删除某个学号 自己测试数据用的 static String deleteByIdSql = "USE students" + "/n" + "DELETE FROM scores WHERE sno = ";}class sqlServer{ private Connection connection = null; //连接接口实例 private Statement statmment = null; //执行静态sql的接口实例 private PreparedStatement preStatement = null; //执行动态sql的接口实例 private ResultSet resSet = null; // sql查询的返回数据集合 String dbName = "students"; //数据库名 String tbName = "scores"; //数据表名 没必要其实 String url = "jdbc:sqlserver://127.0.0.1:1433"; //sqlserver连接地址url String userName = "sa"; //sqlserver的账号名 要在SMSS安全性里面设置 String passWord = "root"; //sqlserver的账号的密码 要在SMSS安全性里面设置 //下面就是按课题要求写的一些静态代码(String字符串类型,在SqlCode.java文件中的全局变量) String createTableSql = SqlCode.createTable; String insertSql = SqlCode.insertValues; String queryAllSql = SqlCode.queryString; String updateBoySql = SqlCode.updateScoreBoy; String updateGrilSql = SqlCode.updateScoreGirl; String delByIdSql = SqlCode.deleteByIdSql; //无参构造函数 初始化建立连接 public sqlServer() { // TODO Auto-generated constructor stub try { //加载数据库驱动 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //DriverManager接口获取连接 this.connection = DriverManager.getConnection(url,userName,passWord); //获取 执行数据库静态SQL语法的接口 this.statmment = connection.createStatement(); if(connection != null) { System.out.println("连接成功!"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //有参构造函数 urlParam初始化建立连接 public sqlServer(String urlParam) { // TODO Auto-generated constructor stub try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); this.connection = DriverManager.getConnection(urlParam); this.statmment = connection.createStatement(); if(connection != null) { System.out.println("连接成功!"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //关闭连接 public void close() throws SQLException { if(resSet != null) { resSet.close(); } if(statmment != null) { statmment.close(); } if(preStatement != null) { preStatement.close(); } if(connection != null) { System.out.println("关闭连接!"); connection.close(); } } //打印输出 ResultSet集合中的数据 public void rsPrint(ResultSet rS) throws SQLException { if(rS == null) return; System.out.println(""); System.out.println("sno"+"| name"+" | ssex"+" | score"); while(rS.next()) { int sno = rS.getInt("sno"); String name = rS.getString("name"); String ssex = rS.getString("ssex"); int score = rS.getInt("score"); System.out.println(sno+" | "+name+" | "+ssex+" | "+score); } } //返回ResultSet集合 public ResultSet queryBySno(int snoId) throws SQLException { String queryByIdString = "" + "USE students" + "/n" + "SELECT * FROM scores" + "/n" + "WHERE scores.sno = ?" + ""; this.preStatement = connection.prepareStatement(queryByIdString); preStatement.setInt(1, snoId); return preStatement.executeQuery(); } //查询全部 public ResultSet queryAll(String querySql) throws SQLException { return statmment.executeQuery(querySql); } //创建数据库 public void generalExc(String sql) throws SQLException { preStatement = connection.prepareStatement(sql); preStatement.executeUpdate(); } //创建数据库 public void createDataBase(String dbName) throws SQLException { String createSql = "CREATE DATABASE "+dbName; preStatement = connection.prepareStatement(createSql);// preStatement.setString(1, dbName); preStatement.executeUpdate(); System.out.println("创建数据库"+dbName+"成功!"); } //删除数据库 public void delDataBase(String dbName) throws SQLException { String deleteSql = "DROP DATABASE "+dbName; preStatement = connection.prepareStatement(deleteSql);// preStatement.setString(1, dbName); preStatement.executeUpdate(); System.out.println("删除数据库"+dbName+"成功!"); } //通过sno学号删除 数据表中的记录 public void delById(int sno) throws SQLException { preStatement = connection.prepareStatement(delByIdSql + sno); preStatement.executeUpdate(); System.out.println("删除记录"+"成功!"); } //创建数据表 public void createTable(String createSql) throws SQLException { statmment.execute(createSql); System.out.println("创建数据表"+"成功!"); } //插入数据到数据表 public void insertValue(String insertSql) throws SQLException { statmment.execute(insertSql); System.out.println("删除数据表"+"成功!"); } //更新数据表中的数据 public void updateValue(String updateSql) throws SQLException { statmment.execute(updateSql); System.out.println("更新完成!"); } //scanner输入指定学号,查询学生信息 public void inputSnoAndQuery() throws SQLException { Scanner inputScanner = new Scanner(System.in); int snoId = inputScanner.nextInt(); rsPrint(queryBySno(snoId)); } //返回值:把ResultSet集合中的数据转换成String类型 (因为后面展示到窗口文本域需要string类型) public String returnString(ResultSet rS) throws SQLException { // TODO Auto-generated method stub StringBuffer myBuffer = new StringBuffer(); int line = 0; while(rS.next()) { if(line == 0) { line++; myBuffer.append("查询结果如下: "+"/n");// myBuffer.append("sno"+"| name"+" | ssex"+" | score"+"/n"); } int sno = rS.getInt("sno"); String name = rS.getString("name"); String ssex = rS.getString("ssex"); int score = rS.getInt("score"); myBuffer.append(sno+" | "+name+" | "+ssex+" | "+score+"/n"); } if(line == 0) myBuffer.append(""); return myBuffer.toString(); } }class window{ //组件 public JFrame sqlWindowFrame; public JPanel PanelSouth; public JPanel PanelNorth; public JTextArea textArea; public JScrollPane scrollPane; public JTextField inpuTextField; //一系列按钮 public JButton customQueryBtn; //执行自定义sql代码的查询按钮 public JButton noResultBtn; //执行没有返回值的sql代码的按钮 比如:create insert delete 这些 public JButton createDBBtn; //创建数据库按钮 public JButton createTBBtn; //创建数据表按钮 public JButton insertBtn; //添加数据按钮 public JButton showBtn; //展示5个学生数据的按钮 public JButton updateBtn; //更新数据的按钮 男-5 女+3 public JButton querySnoBtn; //通过学号查询的按钮 public JLabel labelSouth; //底部标签 public JLabel labelNorth; //顶部标签 public sqlServer myServer; //把sqlServer作为内部类 //窗口构造函数 主要用来初始化组件 public window() { // TODO Auto-generated constructor stub this.sqlWindowFrame = new JFrame("by fishers _(´ཀ`」 ∠)_"); //设置窗体 名字为notePad this.sqlWindowFrame.setLayout(new BorderLayout()); //边界布局方式 this.sqlWindowFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); //设置关闭框 this.sqlWindowFrame.setSize(800,500); this.textArea = new JTextArea(); this.scrollPane = new JScrollPane(textArea); this.inpuTextField = new JTextField(30); this.customQueryBtn = new JButton("执行查询"); this.noResultBtn = new JButton("执行无返回值的sql"); this.createDBBtn = new JButton("创建数据库"); this.createTBBtn = new JButton("创建数据表"); this.insertBtn = new JButton("添加数据"); this.showBtn = new JButton("展示数据"); this.updateBtn = new JButton("更新数据"); this.querySnoBtn = new JButton("查询学号"); this.PanelSouth = new JPanel(); this.PanelNorth = new JPanel(); this.labelSouth = new JLabel("输入sql语法: "); this.labelNorth = new JLabel("内置功能区: "); this.myServer = new sqlServer(); textArea.setFont(new Font("宋体",Font.PLAIN,20)); textArea.setEditable(false); //设置文本域组件不可以编辑 itemAdd(); addListen(); } //添加组件都写在这里 public void itemAdd() { PanelSouth.add(labelSouth); PanelSouth.add(inpuTextField); PanelSouth.add(customQueryBtn); PanelSouth.add(noResultBtn); PanelSouth.add(noResultBtn); PanelNorth.add(labelNorth); PanelNorth.add(createDBBtn); PanelNorth.add(createTBBtn); PanelNorth.add(insertBtn); PanelNorth.add(showBtn); PanelNorth.add(updateBtn); PanelNorth.add(querySnoBtn); sqlWindowFrame.add(scrollPane,BorderLayout.CENTER); sqlWindowFrame.add(PanelSouth,BorderLayout.SOUTH); sqlWindowFrame.add(PanelNorth,BorderLayout.NORTH); sqlWindowFrame.setVisible(true); } //监听方法都写在这里 public void addListen() { //监听自定义查询按钮 customQueryBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub String textString = inpuTextField.getText(); System.out.println(textString); if(textString != null) { try {// myServer.rsPrint(myServer.queryAll(textString)); String queryAns = myServer.returnString(myServer.queryAll(textString)); System.out.println(queryAns); textArea.setText(queryAns); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } }); //监听没有返回值的按钮 noResultBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub String textString = inpuTextField.getText(); System.out.println(textString); if(textString != null) { try { myServer.generalExc(textString); textArea.setText("执行完成!"); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } }); //监听创建数据库按钮 createDBBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub try { myServer.createDataBase("students"); textArea.setText("创建数据库完成!"); } catch (SQLException e1) { // TODO Auto-generated catch block textArea.setText("创建数据库失败,请检查语法是否正确!或当前连接已经存在该数据库!"); e1.printStackTrace(); } } }); //监听创建数据表的按钮 createTBBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub try { myServer.createTable(myServer.createTableSql); textArea.setText("创建数据表完成!"); } catch (SQLException e1) { textArea.setText("创建数据表失败,请检查语法是否正确!或当前数据库中已经存在该数据表!"); e1.printStackTrace(); } } }); //监听插入数据的按钮 insertBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub try { myServer.insertValue(myServer.insertSql); textArea.setText("添加数据完成!"); } catch (SQLException e1) { textArea.setText("添加数据失败,请检查语法是否正确!或当前数据库中已经存在该数据!"); e1.printStackTrace(); } } }); //监听展示数据的按钮 showBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub try { String queryAns = myServer.returnString(myServer.queryAll(myServer.queryAllSql)); System.out.println(queryAns); textArea.setText(queryAns); } catch (SQLException e1) { e1.printStackTrace(); } } }); //监听更新数据的按钮 updateBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub try { myServer.updateValue(myServer.updateBoySql); myServer.updateValue(myServer.updateGrilSql); textArea.setText("更新数据完成!"); } catch (SQLException e1) { // TODO Auto-generated catch block textArea.setText("更新数据失败,请检查语法是否正确!"); e1.printStackTrace(); } } }); //监听通过学号查询数据的按钮 querySnoBtn.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub try { int sno = Integer.valueOf(inpuTextField.getText()); String queryAns = myServer.returnString(myServer.queryBySno(sno));// if(queryAns == " " || queryAns == null) queryAns = "未查到该学生信息";// System.out.println(queryAns); textArea.setText(queryAns); } catch (SQLException e1) { // TODO Auto-generated catch block textArea.setText("查询失败,请检查语法是否正确"); e1.printStackTrace(); } } }); }}//主进程启动public class SqlServerStu { public static void main(String []args) throws SQLException {// String urlParam = "jdbc:sqlserver://127.0.0.1:1433?user=sa&password=root"; //这个连接url好像不能用啊// sqlServer myServer = new sqlServer();// myServer.createDataBase("students"); //创建数据库// myServer.createTable(myServer.createTableSql); //创建数据表// myServer.insertValue(myServer.insertSql); //增// myServer.rsPrint(myServer.queryAll(myServer.queryAllSql)); //查// myServer.rsPrint(myServer.queryBySno(2)); //查// myServer.updateValue(myServer.updateBoySql); //改// myServer.delById(1); //删// myServer.rsPrint(myServer.queryAll(myServer.queryAllSql)); //查// myServer.delDataBase("students"); //删// myServer.close(); //关闭连接// myServer.inputSnoAndQuery();// myServer.updateValue(myServer.updateBoySql);// myServer.delDataBase("students");// myServer.createDataBase("qwertest12"); window myWindow = new window(); //最后还是做成了窗口 orz }}The Apache Tomcat installation at this directory is version 8.5.38. A Tomcat 8.0 installation is expected.<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>欢迎页面</title></head><body> 欢迎使用eclipse部署Tomcat。</body></html> protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); Connection connection = null; //连接接口实例 Statement statmment = null; //执行静态sql的接口实例 PreparedStatement preStatement = null; //执行动态sql的接口实例 ResultSet resultSet = null; //sql查询的返回数据集合 String userName = "sa"; //数据库账号 String passWord = "root"; //数据库密码 String url = "jdbc:sqlserver://127.0.0.1:1433"; //sqlserver连接地址url int sno = 0; String name = null; String ssex = null; int score = 0; //加载数据库驱动 try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } //DriverManager接口获取连接 try { connection = DriverManager.getConnection(url,userName,passWord); //获取 执行数据库静态SQL语法的接口 statmment = connection.createStatement(); if(connection != null) { System.out.println("连接成功!"); } //从数据库中 获取一位同学的个人信息 String querySqlString = "" + "USE students" + "/n" + "SELECT TOP 1* FROM scores"; resultSet = statmment.executeQuery(querySqlString); while(resultSet.next()) { sno = resultSet.getInt("sno"); name = resultSet.getString("name"); ssex = resultSet.getString("ssex"); score = resultSet.getInt("score"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } //用setAttribute设置传递的数据参数 request.setAttribute ( "sno", sno) ; request.setAttribute ( "name", name) ; request.setAttribute ( "ssex", ssex) ; request.setAttribute ( "score", score) ; //重要的是getRequestDispatcher 把数据交给jsp页面响应 request.getRequestDispatcher ( "NewFile.jsp").forward( request , response );//转发到NewFile.jsp，让他去具体响应 }<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%><!DOCTYPE html><html><head><meta charset="utf-8"><title>Insert title here</title></head><body>用户学号：${sno} 用户姓名:${name} 用户性别:${ssex} 用户分数:${score}</body></html>关键词：简单,配置,环境,实战
