数据库连接及增删改查那些事

数据库的连接

例如本机：String url=”jdbc:jtds:sqlserver://10.20.90.6:1433（ip地址）/EC_Digest（数据库名）”;
String driver=”net.sourceforge.jtds.jdbc.Driver”;
Class.forName(driver); //加载驱动
Connection conn=DriverManager.getConnection(url,”sa”,”root123”);//连接数据库，括号里的分别是url，用户名，密码；

---

小试牛刀

增加数据：

@RequestMapping(value = "addlist")
    @ResponseBody
    public String addList(HttpServletRequest request, HttpServletResponse response) throws Exception 
    {
        String id=request.getParameter("id");
        String name=request.getParameter("name");
        String number=request.getParameter("number");
        String dist=request.getParameter("dist");
        String caegory=request.getParameter("caegory");
        String checkSql="insert into czymanage values ('"+id+"','"+name+"','"+number+"','"+dist+"','"+caegory+"')";
        String driver="net.sourceforge.jtds.jdbc.Driver";
        String url="jdbc:jtds:sqlserver://10.20.90.6:1433/EC_Digest";
        Class.forName(driver);
        Connection conn=DriverManager.getConnection(url,"sa","root123");
        Statement stmt=conn.createStatement();
        stmt.executeUpdate(checkSql);
        stmt.close();
        conn.close();
        return id;
    }

删除数据：

@RequestMapping(value = "deletelist")
    @ResponseBody
    public String deleteList(HttpServletRequest request, HttpServletResponse response) throws Exception 
    {
        String id=request.getParameter("id");
        String checkSql="delete from czymanage where proid='"+id+"'";
        String driver="net.sourceforge.jtds.jdbc.Driver";
        String url="jdbc:jtds:sqlserver://10.20.90.6:1433/EC_Digest";
        Class.forName(driver);
        Connection conn=DriverManager.getConnection(url,"sa","root123");
        Statement stmt=conn.createStatement();
        stmt.executeUpdate(checkSql);
        stmt.close();
        conn.close();
        return id;
    }

修改数据：

@RequestMapping(value = "modifylist")
    @ResponseBody
    public String modifylist(ModelMap model,HttpServletRequest request, HttpServletResponse response) throws Exception 
    {
        String id=request.getParameter("id"),
               name=request.getParameter("name"),
               number=request.getParameter("number"),
               dist=request.getParameter("dist"),
               caegory=request.getParameter("caegory"),
               checkSql="update czymanage set proname='"+name+"',pronumber='"+number+"',prodist='"+dist+"',procaegory='"+caegory+"'where proid='"+id+"'",
               driver="net.sourceforge.jtds.jdbc.Driver",
               url="jdbc:jtds:sqlserver://10.20.90.6:1433/EC_Digest";
        Class.forName(driver);
        Connection conn=DriverManager.getConnection(url,"sa","root123");
        Statement stmt=conn.createStatement();
        stmt.executeUpdate(checkSql);

        stmt.close();
        conn.close();       
        return id;
    }

查询数据：

@RequestMapping(value = "querylist")
    public String querylist(ModelMap model,HttpServletRequest request, HttpServletResponse response) throws Exception 
    {
        String query=request.getParameter("query");
        List<listBeans> list = new ArrayList<listBeans>();
        String checkSql="select * from czymanage where proid='"+query+"'";
        String driver="net.sourceforge.jtds.jdbc.Driver";
        String url="jdbc:jtds:sqlserver://10.20.90.6:1433/EC_Digest";
        Class.forName(driver);
        Connection conn=DriverManager.getConnection(url,"sa","root123");
        Statement stmt=conn.createStatement();
        ResultSet rs = stmt.executeQuery(checkSql);
        while(rs.next())
        {
            listBeans listb=new listBeans();
            listb.setId(rs.getString(1));
            listb.setName(rs.getString(2));
            listb.setNumber(rs.getString(3));
            listb.setDist(rs.getString(4));
            listb.setCaegory(rs.getString(5));
            list.add(listb);
        }
        model.put("list", list);
        rs.close();
        stmt.close();
        conn.close();       
        return "digest/product";
    }

---

大家要熟练掌握数据库语句，数据库使用代码千篇一律。